Multiple CountIfs
 

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))


--
SkyFlier

Multiple CountIfs
 

Hi,

Try this instead

=SUMPRODUCT(--(E18:E35="SMOOTHING"),--(K18:K35="JC"))

Its not array entered and in general runs faster than arrays.


--
If this helps, please click the Yes button.

Cheers,
Shane Devenshire


"skyflier" wrote:

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))


--
SkyFlier

Multiple CountIfs
 

.. 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:K3 5)="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
---
"skyflier" wrote:
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))


--
SkyFlier

Multiple CountIfs
 

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))

--
If this helps, please click the Yes button.

Cheers,
Shane Devenshire


"skyflier" wrote:

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))


--
SkyFlier

Multiple CountIfs
 

That can be reduced to:

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

--
Biff
Microsoft Excel MVP


"Shane Devenshire" wrote in
message ...
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))

--
If this helps, please click the Yes button.

Cheers,
Shane Devenshire


"skyflier" wrote:

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))


--
SkyFlier

Multiple CountIfs
 

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


"Shane Devenshire" wrote:

Hi,

Try this instead

=SUMPRODUCT(--(E18:E35="SMOOTHING"),--(K18:K35="JC"))

Its not array entered and in general runs faster than arrays.


--
If this helps, please click the Yes button.

Cheers,
Shane Devenshire


"skyflier" wrote:

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))


--
SkyFlier

Multiple CountIfs
 

The trim worked..... Thanks to all.
--
SkyFlier


"Max" wrote:

.. 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:K3 5)="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
---
"skyflier" wrote:
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))


--
SkyFlier

Multiple CountIfs
 

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
---
"skyflier" wrote:
The trim worked..... Thanks to all.
--
SkyFlier