Thread: Count a column based on multiple criteria

1. I'm working w/ excel 2003/professional. I'm trying to get my first tab to count all entries made in tab 2 where column B contains "FY10/02" and column J contains "STATE" Can anyone give me a formula to count the entries? I know that I definetly have 3 state entries for FY10/02, but it keeps returning blank.

=SUMPRODUCT(--(Sheet2!B1:B11="FY10/02"),--(Sheet2!J1:J11="State"))

3. I tried the formula:
=sumproduct(('sheet2'!\$B\$3:\$B\$65536="FY10/02"),('sheet2'!\$J\$3:\$J\$65536="State"))

And it returned 0 in the cell. I even tried retyping FY10/02 and State, and making sure there weren't any spaces afterwards..just incase. And it's still not counting the 3 entries? I also have one Federal entry too (switching state to federal) that is not being recognized Any ideas? Thank you so much for all your help!!!!!

4. The formula you posted is not the same as the one I posted.

Change it to this
=sumproduct(--('sheet2'!\$B\$3:\$B\$65536="FY10/02"),--('sheet2'!\$J\$3:\$J\$65536="State"))

or this

=SUMPRODUCT((Sheet2!\$B\$3:\$B\$65536="FY10/02")*(Sheet2!\$J\$3:\$J\$65536="State")*1)

5. Originally Posted by mbarron
The formula you posted is not the same as the one I posted.

Change it to this
=sumproduct(--('sheet2'!\$B\$3:\$B\$65536="FY10/02"),--('sheet2'!\$J\$3:\$J\$65536="State"))

or this

=SUMPRODUCT((Sheet2!\$B\$3:\$B\$65536="FY10/02")*(Sheet2!\$J\$3:\$J\$65536="State")*1)

Perfect, worked wonderfully! Thank you again so much!!!!!!... for future reference, what does the -- do?

6. Turns the True or False to 1 or 0 for summing.
Do a search on "unary excel" for the complete explanation.

Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•