Sum in Excel to ignore a text field

I need to sum 5 columns, in each of the columns if the header is blank then the formula inputs "" (here is a copy of one of the columns formulas - =IF($AY$1="","",SUM(AZ5:AZ51)) )

When I try to add up all 5 columns I get a #Value! error.

can you help?
bryanscott53Asked:
Who is Participating?
 
SafetyFishConnect With a Mentor Commented:
My bad, you need quotes around the criteria:

=SUMIF([sum range], "<> "" ",[sum range])
0
 
SafetyFishCommented:
Try Sumif([range], <> "", [range])

<> means does not equal (if you didn't know)
0
 
Rob HensonFinance AnalystCommented:
Your formula:

=IF($AY$1="","",SUM(AZ5:AZ51)))

Though summing column AZ it is looking at the header for column AY, is this correct?

Where are you putting the formula? I am assuming below AZ51 eg in AZ52.

I have tried replicating the formula and it seems to work.

Thanks
Rob H
0
Cloud Class® Course: Microsoft Office 2010

This course will introduce you to the interfaces and features of Microsoft Office 2010 Word, Excel, PowerPoint, Outlook, and Access. You will learn about the features that are shared between all products in the Office suite, as well as the new features that are product specific.

 
Ingeborg Hawighorst (Microsoft MVP / EE MVE)Microsoft MVP ExcelCommented:
Hello,

which 5 columns do you need to sum?

The first part of your IF statement references column AY. The Sum() function in the IF statement references AZ. Is that your intention?

What is the formula that produces the #Value! error?

Sum() works fine on a mix of text and numbers.

Can you post a sample file that presents the problem?

cheers, teylyn
0
 
Rob HensonFinance AnalystCommented:
Just re-read the problem and interpreted slightly different.

I assume you have the formula in say columns AV to AZ and then adding these up but resulting in an error. If one the formulas is generating the blank, this could cause the error although the sum would normally ignore blank or text values.

Try changing the formula to return 0 or sum rather than "".

Thanks
Rob H
0
 
bryanscott53Author Commented:
Thanks safetyfish this worked great
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.