Count text with specific criteria ignoring N/A and blanks

P

Pumpkin Pie

Hello

I have a list of survey names in A3:A726 and peoples names in Y3:Y726.
Column Y also contains N/A and blanks.

I am doing a summary table on the next sheet so I need a formula to show if
it is 'SurveyName="Manchester" then count 'Autumn 09'!Y3:Y726 to show it has
42 names listed against it (ignoring N/As)

Any help would be greatly appreciated.

Many thaks Paula
 
A

Ashish Mathur

Hi,

Try this

=sumproduct((sheet1!A3:A726="Manchester")*(sheet1!Y3:Y726<>"N/A")*(sheet1!Y3:Y726<>""))

I have not tried this but it should work. Also, I have assumed that N/A is
a typed in value and not the N/A Excel error. If it is the Excel error,
then use this

=sumproduct((sheet1!A3:A726="Manchester")*(not(iserror(sheet1!Y3:Y726)))*(sheet1!Y3:Y726<>""))

--
Regards,

Ashish Mathur
Microsoft Excel MVP
www.ashishmathur.com
 
P

Pumpkin Pie

Hi Ashish

You are a star. The second one worked a treat.

Thanks for your quick reply.

Paula
x
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Average number excluding 0 and blanks with text criteria 6
Count the Y's in a filtered column that contains Y and N 1
Count items when specific text and date criteria are met 8
Find criteria based data and display next to a count in another wk 2
SUMPRODUCT / SUMIF on Multiple Worksheets with Multiple Criteria 2
Deleting specific rows with a specific criteria using inputbox 2
Trouble with Subforms, data input, refresh, and requery. 16

Top