Sumproduct Count, Allow Or Two Fields

[Guest]

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.

 

[Peo Sjoblom]

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)

 

[Guest]

Perfect! Simple yet accurate. Thanks!

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)