Counting Occurrences of Data in Multiple Columns

Hi -

Wondering if anyone has a solution to this problem: we work with a piece of software that does social network analysis, meaning, maps the relationships between organizations within a sector, such as HIV/AIDS advocacy in Tanzania. Reports are then published to a .csv file with multiple organizations listed under multiple types of metrics, tby column. So, my need is to be able to identify, based on a target list of organizations, the number of times each of these organizations appear in multiple columns.

As you can see in the attached excel file, I have been doing this manually by placing a 1 next to each of the target organizations which appear in the lists of interest, and then summing these. Ideally, I would love a generic formula that basically calculates the number of times data found in cell A2 is found in column B2:B50. The formula needs to reference the cell, not "APIWC", as the target organization list changes for each network we map.

Thanks! feel free to comment / ask questions if my problem is unclear.


jacob

Hi

This will find the number of times that the value in Column A appears in Clinical Services (Column D):

=SUMPRODUCT(--($D2:$D38=A2)).

You will need to take care with the output from the CSV file - it would be a good idea to TRIM both the data field that you are trying to find and the data column that you are using.

TRIM is straight forward =TRIM(D2) drag down then copy special paste values.

edvwvw

Hi -

Yes, that works perfectly. A couple of quick follow up questions:

1. I have never used the (--( in a formula.. what exactly does this do?
2. How could I alter this formula so as to not count blank cells?

Thanks again!

jacob

Hi
1, -- is the "double unary operator" which is well explained at this site

2.The provided formula will only count the number of times the condition is true, so not to worry.

As for me I'd prefer using =countif($b$2:$b$38),a2) which would probably be faster