| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Multiple Countif results
My heads hurting trying to sort this one out, and the answer will be so
obvious...but can anyone clear this up for me I need to record multiple results based on set criteria being met i.e the total number of Criteria A's for Staff Group B, Criteria A's for Staff Group A and so on. Staff Group A Criteria A Staff Group B Criteria A Staff Group A Criteria A Staff Group B Criteria B Staff Group B Criteria B I've tried =SUMPRODUCT(--(Sheet1!B:B="Staff Group A")*(Sheet1!C:C="Criteria A")) but keep getting #Num Can anyone help FtB |
|
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Multiple Countif results
Hi,
you can not use B:B in a sumproduct function use B2:B1000 for eg. adjust for your needs hth regards from Brazil Marcelo "Fez the Blue" escreveu: My heads hurting trying to sort this one out, and the answer will be so obvious...but can anyone clear this up for me I need to record multiple results based on set criteria being met i.e the total number of Criteria A's for Staff Group B, Criteria A's for Staff Group A and so on. Staff Group A Criteria A Staff Group B Criteria A Staff Group A Criteria A Staff Group B Criteria B Staff Group B Criteria B I've tried =SUMPRODUCT(--(Sheet1!B:B="Staff Group A")*(Sheet1!C:C="Criteria A")) but keep getting #Num Can anyone help FtB |
|
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Multiple Countif results
You're really close... Change the "*" in your formula to ",--" and you will
be there. -- Jim "Fez the Blue" wrote: My heads hurting trying to sort this one out, and the answer will be so obvious...but can anyone clear this up for me I need to record multiple results based on set criteria being met i.e the total number of Criteria A's for Staff Group B, Criteria A's for Staff Group A and so on. Staff Group A Criteria A Staff Group B Criteria A Staff Group A Criteria A Staff Group B Criteria B Staff Group B Criteria B I've tried =SUMPRODUCT(--(Sheet1!B:B="Staff Group A")*(Sheet1!C:C="Criteria A")) but keep getting #Num Can anyone help FtB |
|
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Multiple Countif results
SUMPRODUCT doesn't work with entire col references. Define the smallest range
size (identical size) which sufficient to cover, eg something like (untested): =SUMPRODUCT((Sheet1!B2:B100="Staff Group A")*(Sheet1!C2:C100="Criteria A")) -- Max Singapore http://savefile.com/projects/236895 xdemechanik --- "Fez the Blue" wrote: My heads hurting trying to sort this one out, and the answer will be so obvious...but can anyone clear this up for me I need to record multiple results based on set criteria being met i.e the total number of Criteria A's for Staff Group B, Criteria A's for Staff Group A and so on. Staff Group A Criteria A Staff Group B Criteria A Staff Group A Criteria A Staff Group B Criteria B Staff Group B Criteria B I've tried =SUMPRODUCT(--(Sheet1!B:B="Staff Group A")*(Sheet1!C:C="Criteria A")) but keep getting #Num Can anyone help FtB |
|
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Multiple Countif results
Hello,
I think you want this: =SUMPRODUCT(--(Sheet1!B:B="Staff Group A"), --(Sheet1!C:C="Criteria A")) see if this works. Cheers, Scott "Fez the Blue" wrote: My heads hurting trying to sort this one out, and the answer will be so obvious...but can anyone clear this up for me I need to record multiple results based on set criteria being met i.e the total number of Criteria A's for Staff Group B, Criteria A's for Staff Group A and so on. Staff Group A Criteria A Staff Group B Criteria A Staff Group A Criteria A Staff Group B Criteria B Staff Group B Criteria B I've tried =SUMPRODUCT(--(Sheet1!B:B="Staff Group A")*(Sheet1!C:C="Criteria A")) but keep getting #Num Can anyone help FtB |
|
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Multiple Countif results
Cheers Scott
Worked a treat FtB "Scott" wrote: Hello, I think you want this: =SUMPRODUCT(--(Sheet1!B:B="Staff Group A"), --(Sheet1!C:C="Criteria A")) see if this works. Cheers, Scott "Fez the Blue" wrote: My heads hurting trying to sort this one out, and the answer will be so obvious...but can anyone clear this up for me I need to record multiple results based on set criteria being met i.e the total number of Criteria A's for Staff Group B, Criteria A's for Staff Group A and so on. Staff Group A Criteria A Staff Group B Criteria A Staff Group A Criteria A Staff Group B Criteria B Staff Group B Criteria B I've tried =SUMPRODUCT(--(Sheet1!B:B="Staff Group A")*(Sheet1!C:C="Criteria A")) but keep getting #Num Can anyone help FtB |
|
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Multiple Countif results
Worked a treat
?? Must admit I'm surprised you managed to get Scott's version of the SUMPRODUCT -- which is still using entire col references -- to work. I thought that was the cause of your #NUM! errors in the first place <g -- Max Singapore http://savefile.com/projects/236895 xdemechanik --- |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| CountIf formula results in the formula itself being displayed. | Excel Worksheet Functions | |||
| abdualmohsn | Excel Discussion (Misc queries) | |||
| Multiple COUNTIF formula | Excel Discussion (Misc queries) | |||
| Return Multiple Results with Lookup | Excel Worksheet Functions | |||
| CountIF across multiple sheets in a workbook | Excel Worksheet Functions | |||
Linear Mode
