=SUM(B5:E12*G5:G12)
(CSE, of course)
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!
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
=SUM(B5:E12*G5:G12)
(CSE, of course)
Rick's "mini" blog... http://www.excelfox.com/forum/f22/
.
Want to post a small screen shot? See Part B here.
Try the SumProduct with its native syntax:
=SUMPRODUCT(B5:E11,G5:G11)
as it's devised exactly for this, that is, for multiplying a set of numerical terms.
Assuming too much and qualifying too much are two faces of the same problem.
Rick's "mini" blog... http://www.excelfox.com/forum/f22/
.
Want to post a small screen shot? See Part B here.
Excellent--didn't realize excel do the 'right' thing when multiplying inside sumproduct--I thought the ranges had to be the same size. BTW, =SUMPRODUCT(B5:E11*G5:G11) works, the 'native syntax' =SUMPRODUCT(B5:E11,G5:G11) does not--it generates an error. But it doesn't need to be CSE.
Assuming too much and qualifying too much are two faces of the same problem.
Aladin, that will give you a #VALUE! error as the ranges are not the same size.
Sheet1
B C D E F G H I 5 20 30 40 50 1 #VALUE! 6 24 36 48 60 2 7 28 42 56 70 3 8 32 48 64 80 4 9 36 54 72 90 5 10 40 60 80 100 6 11 44 66 88 110 7
Spreadsheet Formulas
Cell Formula I5 =SUMPRODUCT(B5:E11,G5:G11)
Excel tables to the web >>
Office 2010/365
My bad. I kept reading B5:B11 while writing down B5:E11. My concern clearly does not apply. One of Sum or SumProduct with the * operator is the choice. If the ranges involved do house formula blanks or other text:
=SUM(IF(ISNUMBER(1/(B1:E11*G1:G11)),B1:E11*G1:G11))
confirmed with control+shift+enter, not just enter, would be the way out. The foregoing would behave like the native SumProduct with appropriate ranges.
Assuming too much and qualifying too much are two faces of the same problem.
OK, one more twist. If someone typed a bit of text in C8 of the original example, the =sumproduct(rng*rng) will fail due to the text value on that row. Any way to have sumproduct ignore text in this context? or treat it as a zero?
Like this thread? Share it with others