I'm having some difficulty trying to clean up a spreadsheet and need some help.

I'm using SUMIF to fill a section on my worksheet where i can check for
totals by value (example: A1:A30 is data and B1:B30 is a number 1-5, i then
am tracking the SUM of each number from B1:B30) which is working ok, except
that i occasionally have unused numbers that leave a "0" for a result in my
SUMIF function. IF for example in B1:B30 i have no "2" my formula still
leaves a "0" and that can be very harmful to my book keeping.

Example of Forumla:

{=SUMIF(B1:B30,2,A1:A30)}
Is there then a way to show "N/A" for unused values?
TIA

Hi,

Try this

=if(sumif(B1:B30,2,A1:A30)=0,"",sumif(B1:B30,2,A1: A30))

--
Regards,

Ashish Mathur
Microsoft Excel MVP
www.ashishmathur.com

"Andy H" <Andy wrote in message
...
I'm having some difficulty trying to clean up a spreadsheet and need some
help.

I'm using SUMIF to fill a section on my worksheet where i can check for
totals by value (example: A1:A30 is data and B1:B30 is a number 1-5, i
then
am tracking the SUM of each number from B1:B30) which is working ok,
except
that i occasionally have unused numbers that leave a "0" for a result in
my
SUMIF function. IF for example in B1:B30 i have no "2" my formula still
leaves a "0" and that can be very harmful to my book keeping.

Example of Forumla:

{=SUMIF(B1:B30,2,A1:A30)}
Is there then a way to show "N/A" for unused values?
TIA

This formula seems to work...

=IF(COUNTIF(B1:B30,2),SUMPRODUCT((B1:B30=2)*A1:A30 ),NA())

--
Rick (MVP - Excel)


"Andy H" <Andy wrote in message
...
I'm having some difficulty trying to clean up a spreadsheet and need some
help.

I'm using SUMIF to fill a section on my worksheet where i can check for
totals by value (example: A1:A30 is data and B1:B30 is a number 1-5, i
then
am tracking the SUM of each number from B1:B30) which is working ok,
except
that i occasionally have unused numbers that leave a "0" for a result in
my
SUMIF function. IF for example in B1:B30 i have no "2" my formula still
leaves a "0" and that can be very harmful to my book keeping.

Example of Forumla:

{=SUMIF(B1:B30,2,A1:A30)}
Is there then a way to show "N/A" for unused values?
TIA

Thank you both, i'll try both today and see which one is easier for a rookie
like myself to understand :)