Multi variables in sum if , not working.!?

[Ando999]

Hi
I want to run a table whereby it looks at 2 variables from a data list and populate a table. Eg see below. I want the formulae to look up the spend figure where a match to say Car 1 and Green ( ie 3). Any advice? SUMIF not working!
Thanks alot

					Table
Car Type	Colour	Spend			Green	Red	Orange
Car 1	Green	3		Car 1
Car 2	Red	4		Car 2
Car 2	Red	2		Car 3
Car 3	Blue	1
Car 1	Orange	2

<COLGROUP><COL style="WIDTH: 116pt; mso-width-source: userset; mso-width-alt: 5668" width=155><COL style="WIDTH: 48pt" span=8 width=64><TBODY>
</TBODY>

 

[Jonmo1]

Which version of Excel do you have?
If you have 2007 or higher, you can use SUMIFS

=SUMIFS($C$2:$C$6,$A$2:$A$6,$E2,$B$2:$B$6,F$1)

A2:A6 = Car Types
B2:B6 = Colors
C2:C6 = Spend
E2 = Car Type to look for
F1 = Color to look for

 

[Andrew Poulsom]

In F4 copied down and across:

=SUMIFS($C$4:$C$8,$A$4:$A$8,$E4,$B$4:$B$8,F$3)

 

[Special-K99]

=sumproduct(($e4=$a$3:$a$7)*(f$3=$b$3:$b$7)*($c$3:$c$7))

 

[Ando999]

SumIf - multi variables

Hi

I want to know formula to allow 2 variables to drive my table based on data table. On example I want to know by colour and car type the spend. Ive tried sumif but failed. Please help! !

Thanks

Data				Table
Car Type	Colour	Spend			Green	Red	Orange
Car 1	Green	3		Car 1	Spend	Spend	Spend
Car 2	Red	4		Car 2	Spend	Spend	Spend
Car 2	Red	2		Car 3	Spend	Spend	Spend
Car 3	Blue	1
Car 1	Orange	2

<COLGROUP><COL style="WIDTH: 116pt; mso-width-source: userset; mso-width-alt: 5668" width=155><COL style="WIDTH: 48pt" span=7 width=64><TBODY>
</TBODY>

 

[jai9]

Re: SumIf - multi variables

Hi,

With Data in A16:C21 and Table from E16:H19,

Try in F17,

=SUMPRODUCT(($A$17:$A$21=E17)*($B$17:$B$21=$F$16)*($C$17:$C$21))

and copy down.

Jai

 

[Weazel]

Re: SumIf - multi variables

you could try...assuming your data starts in A1....in F3 under 'GREEN'

=SUMPRODUCT(($A$2:$A$6=$E2)*($B$2:$B$6=F$1)*$C$2:$C$6)

then drag it right and down.

 

[Joe4]

Re: SumIf - multi variables

I merged your two threads together.

Please do not post the same question multiple times. All clarifications, follow-ups, and bumps should be posted back to the original thread. Per forum rules, posts of a duplicate nature will be locked or deleted (rule #9 here: </SPAN>Forum Rules).


Thanks</SPAN>

 

[Ando999]

Re: SumIf - multi variables

Works a treat , thanks