#N/A problem.

apw420

New Member
Joined
Oct 21, 2002
Messages
34
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

Links? Where??
If Excel says you have links but you can't find them, go to Formulas, Name Manager. Look for old links to dead workbooks & delete.

Corticus

Well-known Member
Joined
Apr 30, 2002
Messages
1,579
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
Joined
May 20, 2002
Messages
890
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
Joined
Oct 21, 2002
Messages
34
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?
 

Aladin Akyurek

MrExcel MVP
Joined
Feb 14, 2002
Messages
85,209

ADVERTISEMENT

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
Joined
Oct 21, 2002
Messages
34
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.

Thanks for your help!
AW
 

Aladin Akyurek

MrExcel MVP
Joined
Feb 14, 2002
Messages
85,209

ADVERTISEMENT

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.

Thanks for your help!
AW

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

apw420

New Member
Joined
Oct 21, 2002
Messages
34
It's computed by a VLookUp formula:
=VLOOKUP($A3,'Oct-28'!$2:$68,3,FALSE)

Thanks again.
AW
 

Aladin Akyurek

MrExcel MVP
Joined
Feb 14, 2002
Messages
85,209
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
 

rss5995

New Member
Joined
Oct 28, 2002
Messages
14
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))))
 

Forum statistics

Threads
1,144,741
Messages
5,726,010
Members
422,652
Latest member
Elnene1

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.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

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

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

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
Top