INDIRECT FUNCTION doesn't work in Google Sheets-CodePudding

I'm trying to sum a discontinuous range with SUM, COUNTIF and INDIRECT formulas. It works well in Excel, but when I upload my file in Google Drive and open it, the sum is different.

This is my formula. When I mark with X the cells D3, F3 or H3, I get the sum.

=SUM(COUNTIF(INDIRECT({"D3","F3","H3"},TRUE),"X"))

The same formula in Google Sheets only get the sum of D3, if I modify F3 or H3, the result doesn't change

Is there a way that you can function this formula in Google Sheets or get the sum of discontinuous cells?

Thank you!

CodePudding user response：

In google sheets you can join the cells into a single array using curly brackets - you don't need the indirect:

=countif({D3,F3,H3},"X")

Having said this, GS isn't quite as flexible as Excel with indirect. If you had two different sized ranges like A1:B2 and C1:E3 and tried to combine them, GS would give a 'mismatched row size' error. You could get round it by flattening them like this:

=ArrayFormula(countif({flatten(A1:B2);flatten(C1:E3)},"X"))