Count IF (match category)

The link is not working for me.

To attach a file to your post,
click advanced (next to quick post),
scroll down until you see "manage file",
click that and select "add files" (top right corner).
click "select files" find your file, click "open" click "upload" click 'done" bottom right. click "submit reply"

Once the upload is completed the file name will appear below the input boxes in this window.
You can then close the window to return to the new post screen.

After you upload an actual Excel workbook, please explain why Personal hobbies should return 8. From the picture, I only see one cell in the Personal hobbies row with something in it.

Thanks Ali.

Try this in V41:
=SUMPRODUCT((B$30:B$56=S41)*(D$30:O$56<>"")*(D$57:O$57))

Also, you can shorten the formula in D57 to this:
=SUM(COUNTIF(D30:D56, {"y","x"}))

Sorry guys, was rushed away from a desk.

Don't know why would a file wouldn't load for some of you either... first time it happened!

Apologies if confused anyone with task description, but in it's simplicity all that a formula needs to do is:

1. Look at categories in S Column (one category at a time)
2. Return a number that category was mentioned in Column B (B5:B56)

So if to count 'Personal hobbies' manually, in Column B it was mentioned 8 times (B5,B6,B8,B9,B11,B14,B15,B48)

We not counting "y" or "x" anywhere here, never asked or mentioned about it... I kept original copy with all there is just in case some complicated formula would be used and I would not be able to then replicate it in the original copy...

And thanks for =SUM(COUNTIF(D30:D56, {"y","x"})) @63falcondude !! Much neater approach, love it

You're welcome. Did the suggested formula for V41 (from post #9) do what you were looking for?



Also, upon further review, you are using Data Validation to force the user to enter x or y, so this:

=SUM(COUNTIF(D30:D56, {"y","x"}))

can be made even shorter to this:

=COUNTA(D30:D56)

Thanks for another tip!

No, the suggested for V41 is not the one... it counts the x and y from D30:O56 and all is needed here, as per post #11:

1. Look at categories in S Column (one category at a time)
2. Return a number that category was mentioned in Column B (B5:B56)

So if to count 'Personal hobbies' manually, in Column B it was mentioned 8 times (B5,B6,B8,B9,B11,B14,B15,B48)

We not counting "y" or "x" anywhere here, never asked or mentioned about it... I kept original copy with all there is just in case some complicated formula would be used and I would not be able to then replicate it in the original copy...

Am I confusing somewhere?

In that case, this should suffice:

V41 =COUNTIF(B$5:B$56,S41)

Wha? No Match, no Index, just like that? Blew me away, 63falcondude! Totally... jeez...

Thank you sooo much for this! What can I say...

Thank you...

Happy to help. Thanks for the rep!