COUNTIF based on different cell criteria 1

[PRMiller2]

Hi all,

Need some help with a formula. Here's my data:

GUEST        OOT          RSVP
Pete         Yes          Yes
Paul         No           Yes
Mary         Yes          
Josh         Yes          No

I am trying to write a formula that will total the number of RSVP's received if the guest is out of town (OOT). Seperately, I'll also need to know the number of OOT guests who have RSVPed as yes. We have to segment this out because we have a seperate count for all RSVPs received, regardless of in-town status.

I've run into a wall trying to use the COUNTIF and COUNTIFS functions. Am I just not looking at this correctly?

 

[Scott24x7]

One thing you could do is add a column, and first count the ones who match both criteria with an IF:
Assuming that the columns above are A B and C, in Column D, put this formula in:
=IF(AND(B2="Yes",C2="Yes"),1,0)
Simply copy that into all the Cells in column D.
Then, at the end of the Column D, just do =SUM(D25) (or your real range).
Now you have the count.



Best Regards,
Scott

"Everything should be made as simple as possible, and no simpler."

 

[PRMiller2]

Thanks Scott, that worked perfectly!

 

[bluedragon2]

FAQ68-4725 explains the SUMPRODUCT formula and would work well with your criteria.

[Blue]Blue[/Blue] [Dragon]

If I wasn't Blue, I would just be a Dragon...