Excel 2003 - Count occurence of word in one column when another criteria is matched

Hello,
I am new to the forum and have actually spent hours today reading and testing options for this. I really hope someone can help.

I have a 4000 row set of personnel data. with the following set up
A_______________________B
1 attended_________________Band 1
2 Place booked_____________Band 2
3 attended_________________Band 1
4 blank____________________Band 1
5 attended_________________Band 2
etc

I need to create a calculation that counts the number of "attended" against Band 1, Band 2 and so on
The results would look like this
Band______Attended Status
Band 1_____2
Band 2 ____ 0
I can use a pivot table to manipulate the results - but I need something to build a dashboard with.

Many thanks.

So far, Sumproduct, Countif and various combinations of each have not worked.

Note - I cannot use countifs.

Have a lovely day

C

Originally Posted by benishiryo

hi claudine, welcome to the forum. maybe:
=SUMPRODUCT(($A$2:$A$10="Attended")*($B$2:$B$10="Band 1"))

you can probably refer the red one to a cell reference containing "Band 1"

Hello Back, thank you for the super swift response. I have tried this with some dummy data (in case the master sheet has stray spaces / syntax) and it doesn't work. Must it be absolute values?

Thanks again,

C

.... just to be clear - either using "attended", "Band 1" or the cell references that the words appear in elsewhere gives a #N/A result......note - I am being careful to avoid circular references too...... Many thanks again C

This is the data looking a "bit" tidier

A B
1 Attended Band 1
3 Place Booked Band 2
4 Attended Band1
5 Attended Band 1
6 Attended Band 1
7 Attended Band 1
8 Attended Band 1
9 #N/A Band 2
10 #N/A Band 2
11 #N/A Band 2
12 Place Booked Band 2
13 Place Booked Band 2
14 Place Booked EGU
15 Place Booked EGU
16 Place Booked EGU
17 Place Booked EGU

Hello - thank you thank you thank you.

My "attended status" column is the result of a vlookup (copy - paste special - values). Although it is now not a "live" calculation - it does show #n/a in some of my 4000 rows. This is causing the calulcation to error.

Such an obvious oversight and so many hours wasted!

Thank you for helping me...

C