conditional sumproduct function

[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.

 

[Frank Kabel]

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]

=if(A1="something",counta(A2:A5),sumproduct(1*(A2:A5=A1))
should do the tric

HT
Geof

 

[Aladin Akyurek]

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

HTH
Geoff

Under the interpretation you chose...

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

would also do.