|
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. |
| All times are GMT +1. The time now is 03:35 PM. |
Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
ExcelBanter.com