| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Sumproduct Count, Allow Or Two Fields
I need to count the number of rows of data that match the following criteria:
The columns of date include: Column A - Year i.e. 2006 Column B - Y or N Column C - Y or N Need to count number of rows where year = 2006 and either Column B = Y or Column C = Y. So far I have: =SUMPRODUCT((A2:A100=2006)*((B2:B100="Y")+(C2:C100 ="Y"))) This seems to work correctly if either column B or C are Y but will overcount if they are both Y. Any ideas. Thanks. |
|
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Sumproduct Count, Allow Or Two Fields
Try
=SUMPRODUCT(--(A2:A100=2006),--((B2:B100="Y")+(C2:C100="Y")0)) -- Regards, Peo Sjoblom Excel 95 - Excel 2007 Northwest Excel Solutions www.nwexcelsolutions.com (Remove ^^ from email) "J" wrote in message ... I need to count the number of rows of data that match the following criteria: The columns of date include: Column A - Year i.e. 2006 Column B - Y or N Column C - Y or N Need to count number of rows where year = 2006 and either Column B = Y or Column C = Y. So far I have: =SUMPRODUCT((A2:A100=2006)*((B2:B100="Y")+(C2:C100 ="Y"))) This seems to work correctly if either column B or C are Y but will overcount if they are both Y. Any ideas. Thanks. |
|
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Sumproduct Count, Allow Or Two Fields
Perfect! Simple yet accurate. Thanks!
"Peo Sjoblom" wrote: Try =SUMPRODUCT(--(A2:A100=2006),--((B2:B100="Y")+(C2:C100="Y")0)) -- Regards, Peo Sjoblom Excel 95 - Excel 2007 Northwest Excel Solutions www.nwexcelsolutions.com (Remove ^^ from email) "J" wrote in message ... I need to count the number of rows of data that match the following criteria: The columns of date include: Column A - Year i.e. 2006 Column B - Y or N Column C - Y or N Need to count number of rows where year = 2006 and either Column B = Y or Column C = Y. So far I have: =SUMPRODUCT((A2:A100=2006)*((B2:B100="Y")+(C2:C100 ="Y"))) This seems to work correctly if either column B or C are Y but will overcount if they are both Y. Any ideas. Thanks. |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| retirning count values from calcualted fields | Excel Discussion (Misc queries) | |||
| How do I count something that has two fields that must be true | Excel Discussion (Misc queries) | |||
| using sumproduct in a range of text fields? | Excel Worksheet Functions | |||
| count based on two fields- need as quick as possible | Excel Worksheet Functions | |||
| count based on two fields - need quickly | Excel Worksheet Functions | |||
Linear Mode
