#N/A problem.

[apw420]

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

 

[Corticus]

Hi,

Try:

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

HTH,
Corticus

 

[maxflia10]

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]

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]

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]

Basically, here's a snipet of what it looks like:

Book1
	A	B	C	D	E	F	G
1	27-Oct			28-Oct			TOTALS
2	Revenue	Data1	Data2	Revenue	Data1	Data2	Revenue
3	0.00	0	0	#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]

On 2002-10-28 15:12, apw420 wrote:
Basically, here's a snipet of what it looks like:

Book1
	A	B	C	D	E	F	G
1	27-Oct			28-Oct			TOTALS
2	Revenue	Data1	Data2	Revenue	Data1	Data2	Revenue
3	0.00	0	0	#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]

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

Thanks again.
AW

 

[Aladin Akyurek]

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]

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))))