# #N/A problem.

#### apw420

##### New Member
I'm using this formula to sum up a row of data:
=SUMPRODUCT((\$B\$2:\$CB\$2="Revenue")*(\$B93:\$CB93))

Yet, this data spans the range of the entire month. So on a day where there's no data yet (like tomorrow!) I get a #N/A in the cell. And when it's total using the above formula - I get an #N/A also. Is there anyway to sum it up so it totals what I have so far and ignores the dates that haven't been filled in yet?

Thank you!

AW

### Excel Facts

Spell Check in Excel
Press F7 to start spell check in Excel. Be careful, by default, Excel does not check Capitalized Werds (whoops)

#### Corticus

##### Well-known Member
Hi,

Try:

=If(ISNA(SUMPRODUCT((\$B\$2:\$CB\$2="Revenue")*(\$B93:\$CB93)))=True,"",=SUMPRODUCT((\$B\$2:\$CB\$2="Revenue")*(\$B93:\$CB93)))

HTH,
Corticus

#### maxflia10

##### Well-known Member
On 2002-10-28 14:44, apw420 wrote:
I'm using this formula to sum up a row of data:
=SUMPRODUCT((\$B\$2:\$CB\$2="Revenue")*(\$B93:\$CB93))

Yet, this data spans the range of the entire month. So on a day where there's no data yet (like tomorrow!) I get a #N/A in the cell. And when it's total using the above formula - I get an #N/A also. Is there anyway to sum it up so it totals what I have so far and ignores the dates that haven't been filled in yet?

Thank you!

AW

Try

=sumif(b2:cb2,"=revenue",b93:cb93)

#### apw420

##### New Member
Getting an error with the:
=If(ISNA(SUMPRODUCT((\$B\$2:\$CB\$2="Revenue")*(\$B93:\$CB93)))=True,"",=SUMPRODUCT((\$B\$2:\$CB\$2="Revenue")*(\$B93:\$CB93)))
formula.

And the SumIf formula suggested - would i still need to insert the \$ to keep it constant?

##### MrExcel MVP
On 2002-10-28 14:44, apw420 wrote:
I'm using this formula to sum up a row of data:
=SUMPRODUCT((\$B\$2:\$CB\$2="Revenue")*(\$B93:\$CB93))

Yet, this data spans the range of the entire month. So on a day where there's no data yet (like tomorrow!) I get a #N/A in the cell. And when it's total using the above formula - I get an #N/A also. Is there anyway to sum it up so it totals what I have so far and ignores the dates that haven't been filled in yet?

Thank you!

AW

I gather you have a formula in the range to sum which returns #N/A. If so, care to post that formula?

#### apw420

##### New Member
Basically, here's a snipet of what it looks like:
Book1
ABCDEFG
127-Oct28-OctTOTALS
2RevenueData1Data2RevenueData1Data2Revenue
30.0000#N/A#N/A#N/A\$0.28
Sheet1

I only have it till th 28th of this month, b/c of the problem I'm having.

The formula I posted above is the one I'm using in the revenue 'summation' cell.

AW

##### MrExcel MVP
On 2002-10-28 15:12, apw420 wrote:
Basically, here's a snipet of what it looks like:
Book1
ABCDEFG
127-Oct28-OctTOTALS
2RevenueData1Data2RevenueData1Data2Revenue
30.0000#N/A#N/A#N/A\$0.28
Sheet1

I only have it till th 28th of this month, b/c of the problem I'm having.

The formula I posted above is the one I'm using in the revenue 'summation' cell.

AW

Is an #N/A a manual entry or computed by a formula?

#### apw420

##### New Member
It's computed by a VLookUp formula:
=VLOOKUP(\$A3,'Oct-28'!\$2:\$68,3,FALSE)

Thanks again.
AW

##### MrExcel MVP
On 2002-10-28 15:18, apw420 wrote:
It's computed by a VLookUp formula:
=VLOOKUP(\$A3,'Oct-28'!\$2:\$68,3,FALSE)

Thanks again.
AW

I'd suggest to define a name (e.g.: Drange) that refers to the lookup range and to use the following VLOOKUP formula:

=IF(ISNUMBER(MATCH(\$A3,INDEX(Drange,0,1),0)),VLOOKUP(\$A3,Drange,ColumnIdx,0),"")

and the SUMIF formula Maxflia10 suggests:

=SUMIF(\$B\$2:\$CB\$2,"Revenue",\$B93:\$CB93)
This message was edited by aladin akyurek on 2002-10-29 16:59

##### New Member
Use ISERROR. This is what your formula will look like:
=IF(ISERROR(SUMPRODUCT((\$B\$2:\$CB\$2="Revenue")*(\$B93:\$CB93))),"",(SUMPRODUCT((\$B\$2:\$CB\$2="Revenue")*(\$B93:\$CB93))))

Replies
3
Views
264
Replies
6
Views
165
Replies
10
Views
726
Replies
0
Views
233
Replies
11
Views
576

1,190,766
Messages
5,982,817
Members
439,798
Latest member
tangojuliet

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back