| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
How to marry two working countif formulas?
Hello Team,
I have two countif formulas that work perfectly for my spreedsheet in two different cells. How can I merge them together to count only if they meet both formulas and enter result in a third cell? The formulas are as follows; =COUNTIF(B3:B50,"ARNECOM") =COUNTIF(C3:C50,"="&DATE(2006,1,1))-COUNTIF(C3:C50,"="&DATE(2006,2,1)) Hey, I thank you in advance! Redwing ML |
|
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
How to marry two working countif formulas?
Try:
=SUMPRODUCT((B3:B50="ARNECOM")*(C3:C50=DATE(2006, 1,1))*(C3:C50<DATE(2006,2,1))) -- Max Singapore http://savefile.com/projects/236895 xdemechanik --- "Redwing ML" wrote: Hello Team, I have two countif formulas that work perfectly for my spreedsheet in two different cells. How can I merge them together to count only if they meet both formulas and enter result in a third cell? The formulas are as follows; =COUNTIF(B3:B50,"ARNECOM") =COUNTIF(C3:C50,"="&DATE(2006,1,1))-COUNTIF(C3:C50,"="&DATE(2006,2,1)) Hey, I thank you in advance! Redwing ML |
|
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
How to marry two working countif formulas?
You'd need SUMPRODUCT for that:
=SUMPRODUCT(--(B3:B50="ARNECOM"),--(C3:C50=DATE(2006,1,1)),--(C3:C50<=DATE(2006,2,1)) Does that help? Post back if you have more questions. -------------------------- Regards, Ron (XL2003, Win XP) Microsoft MVP (Excel) "Redwing ML" wrote in message ... Hello Team, I have two countif formulas that work perfectly for my spreedsheet in two different cells. How can I merge them together to count only if they meet both formulas and enter result in a third cell? The formulas are as follows; =COUNTIF(B3:B50,"ARNECOM") =COUNTIF(C3:C50,"="&DATE(2006,1,1))-COUNTIF(C3:C50,"="&DATE(2006,2,1)) Hey, I thank you in advance! Redwing ML |
|
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
How to marry two working countif formulas?
Try this:
=SUMPRODUCT((B3:B50="ARNECOM")*(C3:C50=DATE(2006, 1,1)*(C3:C50<=DATE(2006,1,1)))) -- HTH, RD ================================================== === Please keep all correspondence within the Group, so all may benefit! ================================================== === "Redwing ML" wrote in message ... Hello Team, I have two countif formulas that work perfectly for my spreedsheet in two different cells. How can I merge them together to count only if they meet both formulas and enter result in a third cell? The formulas are as follows; =COUNTIF(B3:B50,"ARNECOM") =COUNTIF(C3:C50,"="&DATE(2006,1,1))-COUNTIF(C3:C50,"="&DATE(2006,2,1)) Hey, I thank you in advance! Redwing ML |
|
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
How to marry two working countif formulas?
Typo in the second date.
Use Max's! -- Regards, RD ----------------------------------------------------------------------------------------------- Please keep all correspondence within the Group, so all may benefit ! ----------------------------------------------------------------------------------------------- "RagDyeR" wrote in message ... Try this: =SUMPRODUCT((B3:B50="ARNECOM")*(C3:C50=DATE(2006, 1,1)*(C3:C50<=DATE(2006,1,1)))) -- HTH, RD ================================================== === Please keep all correspondence within the Group, so all may benefit! ================================================== === "Redwing ML" wrote in message ... Hello Team, I have two countif formulas that work perfectly for my spreedsheet in two different cells. How can I merge them together to count only if they meet both formulas and enter result in a third cell? The formulas are as follows; =COUNTIF(B3:B50,"ARNECOM") =COUNTIF(C3:C50,"="&DATE(2006,1,1))-COUNTIF(C3:C50,"="&DATE(2006,2,1)) Hey, I thank you in advance! Redwing ML |
|
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
How to marry two working countif formulas?
Yikes! Typo...
This =SUMPRODUCT(--(B3:B50="ARNECOM"),--(C3:C50=DATE(2006,1,1)),--(C3:C50<=DATE(2006,2,1)) Should be: =SUMPRODUCT(--(B3:B50="ARNECOM"),--(C3:C50=DATE(2006,1,1)),--(C3:C50<DATE(2006,2,1)) (I removed the last "=" from the formula) -------------------------- Regards, Ron (XL2003, Win XP) Microsoft MVP (Excel) "Ron Coderre" wrote in message ... You'd need SUMPRODUCT for that: =SUMPRODUCT(--(B3:B50="ARNECOM"),--(C3:C50=DATE(2006,1,1)),--(C3:C50<=DATE(2006,2,1)) Does that help? Post back if you have more questions. -------------------------- Regards, Ron (XL2003, Win XP) Microsoft MVP (Excel) "Redwing ML" wrote in message ... Hello Team, I have two countif formulas that work perfectly for my spreedsheet in two different cells. How can I merge them together to count only if they meet both formulas and enter result in a third cell? The formulas are as follows; =COUNTIF(B3:B50,"ARNECOM") =COUNTIF(C3:C50,"="&DATE(2006,1,1))-COUNTIF(C3:C50,"="&DATE(2006,2,1)) Hey, I thank you in advance! Redwing ML |
|
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
How to marry two working countif formulas?
Try this:
=SUMPRODUCT((B3:B50="ARNECOM")*(TEXT(C3:C50,"mmm-yy")="Jan-06")) "Redwing ML" wrote: Hello Team, I have two countif formulas that work perfectly for my spreedsheet in two different cells. How can I merge them together to count only if they meet both formulas and enter result in a third cell? The formulas are as follows; =COUNTIF(B3:B50,"ARNECOM") =COUNTIF(C3:C50,"="&DATE(2006,1,1))-COUNTIF(C3:C50,"="&DATE(2006,2,1)) Hey, I thank you in advance! Redwing ML |
|
#8
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
How to marry two working countif formulas?
Thanks Ron and Team!
This seems to work just fine! However, how come I had to use "SUMPRODUCT" instead of my original "COUNTIF" or mybe even "IF"("THEN".....?? or "IF"("COUNTIF"...etc..etc..? Redwing ML "Ron Coderre" wrote: Yikes! Typo... This =SUMPRODUCT(--(B3:B50="ARNECOM"),--(C3:C50=DATE(2006,1,1)),--(C3:C50<=DATE(2006,2,1)) Should be: =SUMPRODUCT(--(B3:B50="ARNECOM"),--(C3:C50=DATE(2006,1,1)),--(C3:C50<DATE(2006,2,1)) (I removed the last "=" from the formula) -------------------------- Regards, Ron (XL2003, Win XP) Microsoft MVP (Excel) "Ron Coderre" wrote in message ... You'd need SUMPRODUCT for that: =SUMPRODUCT(--(B3:B50="ARNECOM"),--(C3:C50=DATE(2006,1,1)),--(C3:C50<=DATE(2006,2,1)) Does that help? Post back if you have more questions. -------------------------- Regards, Ron (XL2003, Win XP) Microsoft MVP (Excel) "Redwing ML" wrote in message ... Hello Team, I have two countif formulas that work perfectly for my spreedsheet in two different cells. How can I merge them together to count only if they meet both formulas and enter result in a third cell? The formulas are as follows; =COUNTIF(B3:B50,"ARNECOM") =COUNTIF(C3:C50,"="&DATE(2006,1,1))-COUNTIF(C3:C50,"="&DATE(2006,2,1)) Hey, I thank you in advance! Redwing ML |
|
#9
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
How to marry two working countif formulas?
The date range COUNTIF ultimately returns one value.. Consequently, there's
no way to correlate the count of items within the date range with the number of cells that ="ARNECOM". You really need to test each date/field_value pair individually and accumulate the results. In those multiple-criteria circumstances, SUMPRODUCT is usually most appropriate and least complicated approach. Regards, Ron (XL2003, Win XP) Microsoft MVP (Excel) "R edwing ML" wrote in message ... Thanks Ron and Team! This seems to work just fine! However, how come I had to use "SUMPRODUCT" instead of my original "COUNTIF" or mybe even "IF"("THEN".....?? or "IF"("COUNTIF"...etc..etc..? Redwing ML "Ron Coderre" wrote: Yikes! Typo... This =SUMPRODUCT(--(B3:B50="ARNECOM"),--(C3:C50=DATE(2006,1,1)),--(C3:C50<=DATE(2006,2,1)) Should be: =SUMPRODUCT(--(B3:B50="ARNECOM"),--(C3:C50=DATE(2006,1,1)),--(C3:C50<DATE(2006,2,1)) (I removed the last "=" from the formula) -------------------------- Regards, Ron (XL2003, Win XP) Microsoft MVP (Excel) "Ron Coderre" wrote in message ... You'd need SUMPRODUCT for that: =SUMPRODUCT(--(B3:B50="ARNECOM"),--(C3:C50=DATE(2006,1,1)),--(C3:C50<=DATE(2006,2,1)) Does that help? Post back if you have more questions. -------------------------- Regards, Ron (XL2003, Win XP) Microsoft MVP (Excel) "Redwing ML" wrote in message ... Hello Team, I have two countif formulas that work perfectly for my spreedsheet in two different cells. How can I merge them together to count only if they meet both formulas and enter result in a third cell? The formulas are as follows; =COUNTIF(B3:B50,"ARNECOM") =COUNTIF(C3:C50,"="&DATE(2006,1,1))-COUNTIF(C3:C50,"="&DATE(2006,2,1)) Hey, I thank you in advance! Redwing ML |
|
#10
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
How to marry two working countif formulas?
Thanks again Ron, that does make sense.
Have a great day! Redwing ML "Ron Coderre" wrote: The date range COUNTIF ultimately returns one value.. Consequently, there's no way to correlate the count of items within the date range with the number of cells that ="ARNECOM". You really need to test each date/field_value pair individually and accumulate the results. In those multiple-criteria circumstances, SUMPRODUCT is usually most appropriate and least complicated approach. Regards, Ron (XL2003, Win XP) Microsoft MVP (Excel) "R edwing ML" wrote in message ... Thanks Ron and Team! This seems to work just fine! However, how come I had to use "SUMPRODUCT" instead of my original "COUNTIF" or mybe even "IF"("THEN".....?? or "IF"("COUNTIF"...etc..etc..? Redwing ML "Ron Coderre" wrote: Yikes! Typo... This =SUMPRODUCT(--(B3:B50="ARNECOM"),--(C3:C50=DATE(2006,1,1)),--(C3:C50<=DATE(2006,2,1)) Should be: =SUMPRODUCT(--(B3:B50="ARNECOM"),--(C3:C50=DATE(2006,1,1)),--(C3:C50<DATE(2006,2,1)) (I removed the last "=" from the formula) -------------------------- Regards, Ron (XL2003, Win XP) Microsoft MVP (Excel) "Ron Coderre" wrote in message ... You'd need SUMPRODUCT for that: =SUMPRODUCT(--(B3:B50="ARNECOM"),--(C3:C50=DATE(2006,1,1)),--(C3:C50<=DATE(2006,2,1)) Does that help? Post back if you have more questions. -------------------------- Regards, Ron (XL2003, Win XP) Microsoft MVP (Excel) "Redwing ML" wrote in message ... Hello Team, I have two countif formulas that work perfectly for my spreedsheet in two different cells. How can I merge them together to count only if they meet both formulas and enter result in a third cell? The formulas are as follows; =COUNTIF(B3:B50,"ARNECOM") =COUNTIF(C3:C50,"="&DATE(2006,1,1))-COUNTIF(C3:C50,"="&DATE(2006,2,1)) Hey, I thank you in advance! Redwing ML |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Countif not working | Excel Worksheet Functions | |||
| CountIf Array not working | Excel Discussion (Misc queries) | |||
| Countif, not working. | Excel Discussion (Misc queries) | |||
| Countif with nested function not working? | Excel Worksheet Functions | |||
| countif not working | Excel Discussion (Misc queries) | |||
Linear Mode
