MrExcel Publishing
Your One Stop for Excel Tips & Solutions

Sum total of cells with references


Posted by Lori on January 14, 2001 5:52 PM

I am trying to get a grand total for a column that has references in every cell similar to
=IF(ISNA(VLOOKUP("634001",ZipTotal!A$2:B$60,2,0)),"",(VLOOKUP("63401",ZipTotal!A$2:B$60,2,0))).

The column that returns a result from worksheet ZipTotal is formated as a number (referenced columns are in text form and sorted). The Vlookup fields work but I cannot use the sum command.

Can anyone help?


Posted by Aladin Akyurek on January 14, 2001 10:16 PM

What does the SUM function return?

Aladin

Posted by Lori on January 15, 2001 9:25 AM

Each referenced cell returns the total number of applicants per zip codes for a month. I would like the sum to return the total of all zip codes for the month and another sum to return the total zip codes that span multiple months. The normal sum functions (SUM, SUMIF, COUNTIF) don't seem to work. Example =SUM(N8:N160)

Lori

Posted by Lori on January 15, 2001 9:28 AM


Posted by Aladin Akyurek on January 15, 2001 10:28 AM

: =IF(ISNA(VLOOKUP("634001",ZipTotal!A$2:B$60,2,0)),"",(VLOOKUP("63401",ZipTotal!A$2:B$60,2,0))).

What does =SUM(N8:N160) return?

What is the formatting of the cells that contain vlookup-formulas?

Aladin

Posted by Lori on January 15, 2001 7:11 PM

They are formatted in text and sorted.

Posted by Lori on January 15, 2001 7:13 PM

I've also tried having all columns including the results in ZipTotal columns as text but won't total. If I set them all to a number format then the vlookup won't work correctly....right?