Getting total number of occurrences for rows that contain keyword

[kearbear]

Hello,

I am once again perplexed by an excel formula. I've been using the COUNT function to total "confirm" occurrences in a spreadsheet of attendance for a conference, but now I need to keep track of how many of the "confirm"s have been sent a response. We mark the responses with "Yes" or blank cells.

The function I have currently is =COUNT(IF((F29:F76="confirmed")*(H29:H76="Yes") - I know it's incomplete, but the example I'm using in this tutorial does not explain to my knowledge the source of the end of the function : http://office.microsoft.com/en-us/ex...010070480.aspx.

For instance they use =COUNT(IF((A2:A11="South")*(C2:C11="Meat"),D2:D11)), but don't really say why they put in the 'D2:D:11' at the end of the function. I tried using '2,0' and 'H29:H76' (the column I want the information from) but the function keeps returning 0's which isn't the number I get when I count it myself.

I'm probably missing something simple here, but I'm very new to excel functions so be patient with your answers please

Thanks for your help!

[TheCman81]

Hi and welcome

you can use the following formula to get your result

=SUMPRODUCT(($F$29:$F$76="confirmed")*($H$29:$H$76="Yes"))

[kearbear]

Cool that helps a lot!

Does anyone know if it is also possible, assuming I look for people who have not received a response with =(SUMPRODUCT(($F$29:$F$76="confirmed")*($H$29:$H$76=""))), to highlight those people so that it's easier to find them on the list?

[TheCman81]

You can use conditional formatting using the following formula

="confirmed"&""=$F29:$F36&$H29:$H36

Please check this link for a how-to in conditional formatting

http://office.microsoft.com/en-gb/ex...001111661.aspx

[kearbear]

You can use conditional formatting using the following formula

="confirmed"&""=$F29:$F36&$H29:$H36

Please check this link for a how-to in conditional formatting

http://office.microsoft.com/en-gb/ex...001111661.aspx

Awesome that's exactly what I needed Thank you so much!

[TheCman81]

Thanks for the feedback