Non-adjacent cell range in formula

[Kat]

I am using the formula COUNTIF but this would apply to
any formula requiring range input.
Must the range be for adjacent cells?
I am trying to define the range as a grouping of non-
adjacent cells (i.e. B44,D44,F44,H44) but I have been
unsuccessful.
Any suggestions?

Note that setting up a named range does not seem to work
either.

Thanks.

 

[Jason Morin]

COUNTIF and non-contiguous ranges get tricky. You could
use:

=SUMPRODUCT(--(LARGE((B44,D44,F44,H44),{1,2,3,4})="X"))

where "X" represents the item you're counting.

Or perhaps:

=SUM(COUNTIF(INDIRECT({"B","D","F","H"}&"44"),"X"))

HTH
Jason
Atlanta, GA

 

[Kat]

I'm not sure I understand the logic on this. Sum product
would provide me with a number - a sum - of non-adjacent
values. But I am looking for a count of non-numeric
values.

What is LARGE doing that will impact SUMPRODUCT?

=SUMPRODUCT(--(LARGE((B44,D44,F44,H44),{1,2,3,4})="X"))

where "X" represents the item you're counting.

Here I do not understand the use of INDIRECT. Or how to
relate it to my attempt.

=SUM(COUNTIF(INDIRECT({"B","D","F","H"}&"44"),"X"))

Thank you for responding and thank you for clarifying.

Kathy

 

[Jason Morin]

Sorry - the first formula works only for numerical values
and not text values. But the second one should work fine.
INDIRECT is simply converting each cell reference into a
usable cell reference. COUNTIF counts each cell (the
result can only be 1 or 0) and then SUM totals the
results from all for cells.

Jason

 

[Guest]

O my gosh. Jason, you are awesome! I love learning new
functions and this combination is definitely new to me.

Thank you.