Multiple CountIfs

[skyflier]

Trying to count # of times these two conditions are met using formula below
as an array. Answer always 0???

=COUNT(IF((E18:E35="SMOOTHING")*(K18:K35="JC"),0))

 

[Max]

.. Answer always 0???

Its source data quality, probably there are pesky, invisible white spaces
here & there throwing apparent correct matches/counts off

You could use TRIM around the 2 source ranges in your array expression:
=COUNT(IF((TRIM(E18:E35)="SMOOTHING")*(TRIM(K18:K35)="JC"),0))
which should now yield correct results

Or, use TRIM in Shane's suggested non-array expression:
=SUMPRODUCT(--(TRIM(E18:E35)="SMOOTHING"),--(TRIM(K18:K35)="JC"))

Voila? Celebrate with us, click the YES buttons below in all responses ..
--
Max
Singapore
http://savefile.com/projects/236895
Downloads:25,000 Files:300 Subscribers:70
xdemechanik

 

[Shane Devenshire]

Hi,

Although there is nothing really wrong with your formula, it should give the
correct results, a better way to phrase it would be:

=SUM(IF((E18:E35="SMOOTHING")*(K18:K35="JC"),1,0))

Array entered.

If your formula is failing to return the correct results it is probably
because it isn't array entered Press Shift+Ctrl+Enter to enter it.

=COUNT(IF((E18:E35="SMOOTHING")*(K18:K35="JC"),0))

 

[T. Valko]

That can be reduced to:

=SUM((E18:E35="SMOOTHING")*(K18:K35="JC"))

 

[skyflier]

None of these worked. I am adding a portion of the data.


SMOOTHING 4/20/2009 0.20 42 588 SM JC
SMOOTHING 4/20/2009 1.00 37 2072 POD DS
HOT 4/23/2009 1.00 37 2072 POD DS
HOT 4/23/2009 1.00 37 2072 POD DS
HOT 4/23/2009 1.00 37 2072 POD DS
HOT 4/23/2009 1.00 37 2072 POD DS
HOT 4/23/2009 1.00 37 2072 POD DS
HOT 4/23/2009 1.00 37 2072 POD DS
HOT 4/23/2009 1.00 37 2072 POD DS
WARM 4/23/2009 1.00 31.6 2844 SO SH
SMOOTHING 4/22/2009 0.40 36 936 SM NB
SMOOTHING 4/20/2009 2.00 35 3920 SM NB
SMOOTHING 4/23/2009 0.25 37 518 SM LB
SMOOTHING 4/16/2009 1.00 40.3 2216.5 SM JC
SMOOTHING 4/16/2009 0.60 40.3 1329.9 POD JC
SMOOTHING 4/16/2009 1.00 40.3 2216.5 SM JC
WARM 4/23/2009 0.29 35.3 564.8 SO DS
WARM 4/23/2009 0.29 35.3 564.8 SO DS

If I use the =sumproductxxxxxx formula, is give a "FALSE"
All other formulas as array return 0

 

[skyflier]

The trim worked..... Thanks to all.

 

[Max]

Good to hear. Spare a moment to click the YES buttons (like the ones below)
in all responses which help. Fly high ...
--
Max
Singapore
http://savefile.com/projects/236895
Downloads:25,000 Files:300 Subscribers:70
xdemechanik