can't find the formula

**jfhawk06** · 03-25-2005, 02:55 PM

Hi everyone,

I'd first like to thank you all for your time and consideration in this matter. I believe my inquiry is rather simple, but I have (obviously) been unable to find the answer. I am using a marketing tracking spreadsheet at work. I have a sheet used for inputting contact information (name, city, state, etc.). There is a column on this particular sheet that is titled "new client" and i'm using a drop box with (yes, no). On an alternate sheet, I've set up an analysis of the input data. On this analysis sheet, I have all the cities listed in a column, and the adjacent column in titled "number of clients". I want excel to be able to recognize the cities in the previous sheet and count the "yes" inputs in the new client column. How can I do this?

Joe

**jfhawk06** · 03-25-2005, 03:09 PM

anyone??

Joe

**duane** · 03-25-2005, 03:10 PM

if the contact information is on sheet 2, and the city is in column a rows 1:100 and the yes/no is in column e rows 1:100, while on the alternate sheet the city is listed in column a

on alternate sheet for row 1

=sumproduct((sheet2!a$1:a$100=a1)*(sheet2!e$1:e$100="yes"))

will result in total for this city on sheet 2 with a yes