Formula Problem

[Jason]

Can anybody tell me why they think this formula isn't working. I have
similar formulas in adjacent worksheets that work fine.

=SUMPRODUCT((E25:E5000)*(F25:F5000=9)*(G25:G5000=6.5))

I get the #VALUE! error.

 

[Gary''s Student]

You do not have a formula problem. You probably have a data problem. For
example, if E25 thru G30 contain:

1 0 0
1 0 0
1 9 0
2 9 6.5
1 0 0
1 0 0

Your formula correctly returns 2

 

[Tom Hutchins]

Your data problem may be a cell in column E which contains text instead of a
number.

Hope this helps,

Hutch

 

[Jason]

It is a small amount of data. Here is the actual data I'm trying to
use: Columns are E,F and G. Column E is time.

Thanks


0:46 9 $6.5
0:45 11 $6.5
0:45 9 $6.5
0:46 9 $6.5
0:47 10 $6.5

0:47 9 $6.5
0:48 9 $6.5
0:48 9 $6.5
0:36 1 $6.5
0:48 9 $6.5
0:45 10 $6.5

0:54 9 $6.5
0:48 9 $6.5
0:49 9 $6.5

 

[GoBow777]

;642351']Can anybody tell me why they think this formula isn't working.
I have
similar formulas in adjacent worksheets that work fine.

=SUMPRODUCT((E25:E5000)*(F25:F5000=9)*(G25:G5000=6.5))

I get the #VALUE! error.

No idea why you’re getting the VALUE# error.

To see how Excel is interpreting your formula, click Tools/Formula
Auditing/Evaluate Formula.

 

[Jason]

I don't see a "tools." (Excel 2007)

But I did discover that the formula will work until I get to the empty
row. If I include data up to the first empty row it works fine. If I
try to include data that goes across the empty rows it fails.
Strange, because the exact same formula works on other spreadsheets.

;642351']Can anybody tell me why they think this formula isn't working.
I have
similar formulas in adjacent worksheets that work fine.

=SUMPRODUCT((E25:E5000)*(F25:F5000=9)*(G25:G5000=6.5))

I get the #VALUE! error.

No idea why you’re getting the VALUE# error.

To see how Excel is interpreting your formula, click Tools/Formula
Auditing/Evaluate Formula.

 

[Jason]

I fixed it by doing the following in the E column:

=IF(D28="",0,D28-C28)

As opposed to:

=if(D28="","",D28-C28)

I don't see a "tools." (Excel 2007)

But I did discover that the formula will work until I get to the empty
row. If I include data up to the first empty row it works fine. If I
try to include data that goes across the empty rows it fails.
Strange, because the exact same formula works on other spreadsheets.

;642351']Can anybody tell me why they think this formula isn't working.
I have
similar formulas in adjacent worksheets that work fine.

=SUMPRODUCT((E25:E5000)*(F25:F5000=9)*(G25:G5000=6.5))

I get the #VALUE! error.

No idea why you’re getting the VALUE# error.

To see how Excel is interpreting your formula, click Tools/Formula
Auditing/Evaluate Formula.