countif formula with two data sources

Set up a little table, say in column E, with Adult in E2 and Child in E3. Then put Yes in F1 and No in G1, and then you can put this formula in F2:

=COUNTIFS($B:$B,$E2,$C:$C,F$1)

Copy this across into G2, then copy F2:G2 into F3:G3, and then you will have a summary table of all counts.

Hope this helps.

Pete

I'm so sorry I can't even do this forum correctly. I think I've just uploaded the spread sheet.

Pete - I tried the formula but it didn't work. My cells for Adult/Child are from B5:B110 and I have the word Adult is U1 and the word Child in U2. Then I have the Yes/No is C5:C110 and the word Yes in V1 and No in V2.

I'm sorry this is hard work. Completely my fault!

I don't know how to find out which version I'm using. Is there an easy way to find out please?

It's Microsoft Office 365

Put No in W1 (i.e. to the right of Yes), and put Adult in U2 and Child in U3, so that cell U1 is empty.

Then you can use this formula in cell V2:

=COUNTIFS($B:$B,$U2,$C:$C,V$1)

Copy across and down as required, You should get your results like this (it might not line up exactly right):

……...…….Yes ….. No
Adult …. 33 …... 0
Child …… 11 ….. 1

Hope this helps.

Pete

put this into cell f9 COUNTIFS($C$5:$C$109,$V$1,$B$5:$B$109,$U1) and copy to f10, btw your excel skills aren't so bad as you are using dropdown validation boxes!

you should get 33 adults and 11 kids. enjoy your wedding day.

Pete thank you so much!!! This has saved me so much time. Thank you thank you!!!!!!

You're welcome.

If that takes care of your original question, please take a moment to select Thread Tools from the menu above and to the right of your first post in this thread, and mark the thread as SOLVED.

You might also like to know that you can directly thank those who have helped you by clicking on the small "star" icon located in the lower left corner of a post that you have found to be helpful (not just in this thread - for any post that has helped you). This also adds to the reputation of the poster (the small green bars in the poster's profile).

Pete