conditional sumproduct function

  • Thread starter Thread starter Soe
  • Start date Start date
S

Soe

here is the formula,
=sumproduct(1*(A2:A5=A1))
here is the data
A
1 a
2 b
3 a
4 b
5 a

this formula counts (in the range A2:A5) those equal to value in A1.
However, if A1="something", then I like to count all regardless of their
values. Meaning, if A1="a", then result = 2, if A1="something", then i need
result to be 4.
Pls take note i need to keep sumproduct formula as there are long parameters
in the function.
 
Hi Soe
one way: try
=SUMPRODUCT(--(A2:A5=A1)+(A1="something))
this will count blank cells as well

If you want to prevent this try
=SUMPRODUCT(--(A2:A5=A1)+(A1="something)*(A2:A5<>""))
will count only non blanks

Frank
 
xlbo said:
=if(A1="something",counta(A2:A5),sumproduct(1*(A2:A5=A1)))
should do the trick

HTH
Geoff
Click to expand...

Under the interpretation you chose...

=IF(A1="something",COUNTA(A2:A5),COUNTIF(A2:A5,A1))

would also do.
 
Back
Top