Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#8
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
countifs with multiple dates | Excel Worksheet Functions | |||
Countifs to Sumproduct for 2003 - was Simple Countifs.. from Fr | Excel Worksheet Functions | |||
Countifs and multiple columns... | Excel Worksheet Functions | |||
Multiple countifs | Excel Worksheet Functions | |||
COUNTIFs with multiple criteria | Excel Discussion (Misc queries) |