Count text with specific criteria ignoring N/A and blanks

[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

 

[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

 

[Pumpkin Pie]

Hi Ashish

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

Thanks for your quick reply.

Paula
x

 

[Ashish Mathur]

Thank you. Glad I could help

--
Regards,

Ashish Mathur
Microsoft Excel MVP
www.ashishmathur.com