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.

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.

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.