Say I have a table with the following data:
Column A Description Column B Description
Apples Apples
Apples Bananas
Bananas Oranges
Bananas Apples
Apples Bananas
Oranges Oranges
Bananas Apples
I'm trying to create a Countifs function that counts how many times each description in both columns occurs, but if the description occurs both in column A and column B in the same row, I only want to count it once. For example, in the first row apple appears both in column A and B. I only want to count apple once for that row. If a row contains, for example, Bananas and Oranges, then I want to count one banana and one orange.
I have tried the following formulas =COUNTIFS('Column A Description'!A$1:AH$5000,Analysis!C25)+COUNTIFS('Column B Description'!B$!:B$5000,Analysis!C25,'Column B'!B$1:B$5000,"<>"&'Column A Description'!A$1:A$5000)
and
=COUNTIFS('Column A Description'!A$1:A$5000,Analysis!C24)+COUNTIFS('Column B Description'!A$1:A$5000,Analysis!C24)-COUNTIF('Column B Description'!B$1:B$5000,"=A$1:A$5000")
The reference to C24 is a listing to a certain description out of a list of descriptions in a separate table. The first formula didn't work because it has multiple criterion for one range. The second formula didn't work because it counts blacks in the final countif.
Does anyone have any suggestions?
Bookmarks