R
RichK
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.
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.