summing an array based on row and column criteria? sumproduct?

[mikeross]

I'll try to keep this simple, so let's say this is our data

[TABLE="width: 693"]
<tbody>[TR]
[TD="width: 99"][/TD]
[TD="width: 99"]salesunits[/TD]
[TD="class: xl63, width: 99"] salesdollars[/TD]
[TD="width: 99"]salesunits[/TD]
[TD="class: xl63, width: 99"] salesdollars[/TD]
[TD="width: 99"]salesunits[/TD]
[TD="class: xl63, width: 99"] salesdollars[/TD]
[/TR]
[TR]
[TD]orange[/TD]
[TD="align: right"]4[/TD]
[TD="class: xl63"] $ 16.00[/TD]
[TD="align: right"]5[/TD]
[TD="class: xl63"] $ 20.00[/TD]
[TD="align: right"]1[/TD]
[TD="class: xl63"] $ 4.00[/TD]
[/TR]
[TR]
[TD]pear[/TD]
[TD="align: right"]1[/TD]
[TD="class: xl63"] $ 2.00[/TD]
[TD="align: right"]3[/TD]
[TD="class: xl63"] $ 6.00[/TD]
[TD="align: right"]10[/TD]
[TD="class: xl63"] $ 20.00[/TD]
[/TR]
[TR]
[TD]lemon[/TD]
[TD="align: right"]20[/TD]
[TD="class: xl63"] $ 60.00[/TD]
[TD="align: right"]15[/TD]
[TD="class: xl63"] $ 45.00[/TD]
[TD="align: right"]100[/TD]
[TD="class: xl63"] $ 300.00[/TD]
[/TR]
</tbody>[/TABLE]




and you wanted a formula that returned the number of sales units of apples that have been sold in this entire table would be... what formula could you use? the answer to this would be 10 units (for $40 total)

edit: (and a formula that you could drag down to see pear and lemon sales units/dollars/etc... like they won't always be in the same order)


thank you so much! I've been trying different sumproduct/sumif variations for a long time now at work and can't figure anything out

 

[Marcelo Branco]

Hi and welcome to Mr Excel Forum

Maybe...

A B C D E F G H I J (headers in row 1)
[TABLE="class: grid, width: 700"]
<TBODY>[TR]
[TD="width: 64, bgcolor: transparent"][/TD]
[TD="width: 69, bgcolor: transparent"]salesunits
[/TD]
[TD="width: 80, bgcolor: transparent"]salesdollars
[/TD]
[TD="width: 69, bgcolor: transparent"]salesunits
[/TD]
[TD="width: 80, bgcolor: transparent"]salesdollars
[/TD]
[TD="width: 69, bgcolor: transparent"]salesunits
[/TD]
[TD="width: 80, bgcolor: transparent"]salesdollars
[/TD]
[TD="width: 64, bgcolor: transparent"][/TD]
[TD="width: 64, bgcolor: transparent"]Units
[/TD]
[TD="width: 64, bgcolor: transparent"]Dollars
[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]orange
[/TD]
[TD="bgcolor: transparent, align: right"]4
[/TD]
[TD="bgcolor: transparent, align: right"]16
[/TD]
[TD="bgcolor: transparent, align: right"]5
[/TD]
[TD="bgcolor: transparent, align: right"]20
[/TD]
[TD="bgcolor: transparent, align: right"]1
[/TD]
[TD="bgcolor: transparent, align: right"]4
[/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent, align: right"]10
[/TD]
[TD="bgcolor: transparent, align: right"]40
[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]pear
[/TD]
[TD="bgcolor: transparent, align: right"]1
[/TD]
[TD="bgcolor: transparent, align: right"]2
[/TD]
[TD="bgcolor: transparent, align: right"]3
[/TD]
[TD="bgcolor: transparent, align: right"]6
[/TD]
[TD="bgcolor: transparent, align: right"]10
[/TD]
[TD="bgcolor: transparent, align: right"]20
[/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent, align: right"]14
[/TD]
[TD="bgcolor: transparent, align: right"]28
[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]lemon
[/TD]
[TD="bgcolor: transparent, align: right"]20
[/TD]
[TD="bgcolor: transparent, align: right"]60
[/TD]
[TD="bgcolor: transparent, align: right"]15
[/TD]
[TD="bgcolor: transparent, align: right"]45
[/TD]
[TD="bgcolor: transparent, align: right"]100
[/TD]
[TD="bgcolor: transparent, align: right"]300
[/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent, align: right"]135
[/TD]
[TD="bgcolor: transparent, align: right"]405
[/TD]
[/TR]
</TBODY>[/TABLE]

Formula in I2
=SUMPRODUCT(--(ISNUMBER(SEARCH(I$1,$B$1:$G$1))),$B2:$G2)

copy across to J2 and down

M.

 

[Dave Patton]

Excel Workbook
	A	B	C	D	E	F	G
1	Criteria	Salesunits	Salesdollars
2	orange	20	80
3
4	orange	20	80
5	pear	28	56
6	lemon	270	810
7		318	946
8
9		Salesunits	Salesdollars	salesunits	salesdollars	salesunits	salesdollars
10	orange	4	$16.00	5	$20.00	1	$4.00
11	pear	1	$2.00	3	$6.00	10	$20.00
12	lemon	20	$60.00	15	$45.00	100	$300.00
13	orange	4	$16.00	5	$20.00	1	$4.00
14	pear	1	$2.00	3	$6.00	10	$20.00
15	lemon	20	$60.00	15	$45.00	100	$300.00
16		50	156	46	142	222	648
17
4a

Excel 2003

Cell Formulas
Range	Formula
B2	=SUMIF($A$10:$A$16,$A2,B10:B16)+SUMIF($A$10:$A$16,$A2,D10:D16)+SUMIF($A$10:$A$16,$A2,F10:F16)
B4	=SUMPRODUCT(($A$10:$A$16=$A4)*($B$9:$G$9=$B$1)*($B$10:$G$16))
B16	=SUM(B10:B15)
C2	=SUMIF($A$10:$A$16,$A2,C10:C16)+SUMIF($A$10:$A$16,$A2,E10:E16)+SUMIF($A$10:$A$16,$A2,G10:G16)
C4	=SUMPRODUCT(($A$10:$A$16=$A4)*($B$9:$G$9=$C$1)*($B$10:$G$16))

 

[mikeross]

thank you both! I used Dave Patton's method (from cell B4 and C4) and it worked fine. I added a bunch more criteria like less than/greater than a date, day of week, etc and it all worked perfectly.