I want to sum (by month) sales by person and multiply by the price to get total sales by month.
I thought something like {=SUM(SUM(B5:E12)*G5:G12)} or just plain {=SUM(B5:E12)*G5:G12} would work, but it doesn't, as you can see in the cell identified as "Wrong". The cell marked "Right" is a simple sum of the extended products above it--I added the sales in weeks 1 thru 4 then multiplied the total by the price for each row. Is there an array formula or some other approach that won't involve extra/hidden rows to accomplish the task? It needs to span blank rows and text without blowing up. In a test, I essentially created the Red Team block and was able to get the correct answer. However, it may have been dumb luck--once I edited or added to it so that it was more realistic like what I've shown here it quit working. I'm trying to avoid a normal formula like =sum(B5:E5)*G5+sum(B6:E6)* G6+.... as there are quite a few rows an this becomes immediately tedious. Of course, if you have some other slick way of doing this, I'd be happy to see that too!
I thought something like {=SUM(SUM(B5:E12)*G5:G12)} or just plain {=SUM(B5:E12)*G5:G12} would work, but it doesn't, as you can see in the cell identified as "Wrong". The cell marked "Right" is a simple sum of the extended products above it--I added the sales in weeks 1 thru 4 then multiplied the total by the price for each row. Is there an array formula or some other approach that won't involve extra/hidden rows to accomplish the task? It needs to span blank rows and text without blowing up. In a test, I essentially created the Red Team block and was able to get the correct answer. However, it may have been dumb luck--once I edited or added to it so that it was more realistic like what I've shown here it quit working. I'm trying to avoid a normal formula like =sum(B5:E5)*G5+sum(B6:E6)* G6+.... as there are quite a few rows an this becomes immediately tedious. Of course, if you have some other slick way of doing this, I'd be happy to see that too!
| Excel 2010 | |||||||||||
|---|---|---|---|---|---|---|---|---|---|---|---|
| A | B | C | D | E | F | G | H | I | |||
| 1 | Sales | ||||||||||
| 2 | Jan | Price | |||||||||
| 3 | wk1 | wk2 | wk3 | wk4 | Jan | Ext Jan | |||||
| 4 | Red Team | ||||||||||
| 5 | Joe | 1 | 1 | 1 | 1 | 5 | 20 | ||||
| 6 | Mary | 2 | 9 | 2 | 9 | 2 | 44 | ||||
| 7 | Fred | 5 | 5 | 4 | 4 | 5 | 90 | ||||
| 8 | |||||||||||
| 9 | Blue Team | 0 | |||||||||
| 10 | Jane | 1 | 2 | 3 | 3 | 6 | 54 | ||||
| 11 | Marge | 5 | 6 | 3 | 3 | 3 | 51 | ||||
| 12 | Kevin | 8 | 9 | 3 | 3 | 5 | 115 | ||||
| 13 | |||||||||||
| 14 | 2418 | << Wrong | Right>> | 374 | |||||||
Sheet1 | |||||||||||
