SUMPRODUCT Acting Quirky

16-SEPT-2005

Hi.

I am trying to sum an array (column)of numbers based on the contents of
another column via function SUMPRODUCT. It works fine for one column
and errors in the next.

The first column summation is formatted time values and the second is
decimal/numeric values.

Here is a mock-up of my spreadsheet. Be sure to first click on "fixed
font" (not proportional) to get the columns to line up correctly.

In the following example the problem is cell "G11"! Also, any row with
calculated values is shown twice to illustrate formula and displayed
result.


| A | B | C | D | E | F
|

+-------+----+-----+-----+--------------------+--------------------+----
1 |date |day |start|end | elapsed time | text
|

+-------+----+-----+-----+--------------------+--------------------+----
2 |9/21/05|sun | 8:30|11:00| 2:30 | ya-da
|

+-------+----+-----+-----+--------------------+--------------------+----
3 | | |13:40|14:10| 0:30 | blah
|

+-------+----+-----+-----+--------------------+--------------------+----
4 | | | end | end | SUM(e2:e3) |
=e4*24|

+-------+----+-----+-----+--------------------+--------------------+----
calc.4 end end 3:00
3.00

+-------+----+-----+-----+--------------------+--------------------+----
5 |9/22/05|mon | 9:00|10:15| 1:15 | ya-da
|

+-------+----+-----+-----+--------------------+--------------------+----
6 | | |14:30|17:30| 3:00 | blah
|

+-------+----+-----+-----+--------------------+--------------------+----
7 | | | end | end | SUM(e5:e6) |
=e7*24|

+-------+----+-----+-----+--------------------+--------------------+----
calc.7 end end 4:15
4.25

+-------+----+-----+-----+--------------------+--------------------+----
8 |9/23/05|tues| 7:30|11:15| 3:45 | ya-da
|

+-------+----+-----+-----+--------------------+--------------------+----
9 | | |11:15|12:00| 0:45 | blah
|

+-------+----+-----+-----+--------------------+--------------------+----
10 | | | end | end | SUM(e8:e9) |
=e10*24|

+-------+----+-----+-----+--------------------+--------------------+----
calc10 end end 4:30
4.50

+-------+----+-----+-----+--------------------+--------------------+----
| | | | |=SUMPRODUCT( |=SUMPRODUCT(
|
| | | |TOTAL| ($c1:$c10="end")* | ($c1:$c10="end")*
|
11 | | | | | ($d1:$d10="end")* | ($d1:$d10="end")*
|
| | | | | (e1:e10)) | (e1:e10))
|

+-------+----+-----+-----+--------------------+--------------------+----
| | | | | |
|
calc11 TOTAL 11:45 $VALUE!

| | | | | |
|

+-------+----+-----+-----+--------------------+--------------------+----


If the row and column formatting does not hold true just cut and paste
into Notepad setting font to "Courier" size 8. That should clean it up
visually.

Thanks for the help.

--Rich K.

If the row and column formatting does not hold true just cut and paste
into Notepad setting font to "Courier" size 8. That should clean it up
visually.

Didn't help!

Biff

"RichK" wrote in message
oups.com...

16-SEPT-2005

Hi.

I am trying to sum an array (column)of numbers based on the contents of
another column via function SUMPRODUCT. It works fine for one column
and errors in the next.

The first column summation is formatted time values and the second is
decimal/numeric values.

Here is a mock-up of my spreadsheet. Be sure to first click on "fixed
font" (not proportional) to get the columns to line up correctly.

In the following example the problem is cell "G11"! Also, any row with
calculated values is shown twice to illustrate formula and displayed
result.


| A | B | C | D | E | F
|

+-------+----+-----+-----+--------------------+--------------------+----
1 |date |day |start|end | elapsed time | text
|

+-------+----+-----+-----+--------------------+--------------------+----
2 |9/21/05|sun | 8:30|11:00| 2:30 | ya-da
|

+-------+----+-----+-----+--------------------+--------------------+----
3 | | |13:40|14:10| 0:30 | blah
|

+-------+----+-----+-----+--------------------+--------------------+----
4 | | | end | end | SUM(e2:e3) |
=e4*24|

+-------+----+-----+-----+--------------------+--------------------+----
calc.4 end end 3:00
3.00

+-------+----+-----+-----+--------------------+--------------------+----
5 |9/22/05|mon | 9:00|10:15| 1:15 | ya-da
|

+-------+----+-----+-----+--------------------+--------------------+----
6 | | |14:30|17:30| 3:00 | blah
|

+-------+----+-----+-----+--------------------+--------------------+----
7 | | | end | end | SUM(e5:e6) |
=e7*24|

+-------+----+-----+-----+--------------------+--------------------+----
calc.7 end end 4:15
4.25

+-------+----+-----+-----+--------------------+--------------------+----
8 |9/23/05|tues| 7:30|11:15| 3:45 | ya-da
|

+-------+----+-----+-----+--------------------+--------------------+----
9 | | |11:15|12:00| 0:45 | blah
|

+-------+----+-----+-----+--------------------+--------------------+----
10 | | | end | end | SUM(e8:e9) |
=e10*24|

+-------+----+-----+-----+--------------------+--------------------+----
calc10 end end 4:30
4.50

+-------+----+-----+-----+--------------------+--------------------+----
| | | | |=SUMPRODUCT( |=SUMPRODUCT(
|
| | | |TOTAL| ($c1:$c10="end")* | ($c1:$c10="end")*
|
11 | | | | | ($d1:$d10="end")* | ($d1:$d10="end")*
|
| | | | | (e1:e10)) | (e1:e10))
|

+-------+----+-----+-----+--------------------+--------------------+----
| | | | | |
|
calc11 TOTAL 11:45 $VALUE!

| | | | | |
|

+-------+----+-----+-----+--------------------+--------------------+----


If the row and column formatting does not hold true just cut and paste
into Notepad setting font to "Courier" size 8. That should clean it up
visually.

Thanks for the help.

--Rich K.

You are right; the formatting does not hold. (ugh!)

Is there a place to send a text file for your (and this google group's)
review?

Thanks.

--Rich K.