Multi variables in sum if , not working.!?

A

Ando999

New Member
Joined
Jan 9, 2012
Messages
11
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="width: 500"]
<COLGROUP><COL style="WIDTH: 116pt; mso-width-source: userset; mso-width-alt: 5668" width=155><COL style="WIDTH: 48pt" span=8 width=64><TBODY>[TR]
[TD="class: xl25, width: 155, bgcolor: white"] [/TD]
[TD="class: xl25, width: 64, bgcolor: white"] [/TD]
[TD="class: xl25, width: 64, bgcolor: white"] [/TD]
[TD="class: xl25, width: 64, bgcolor: white"] [/TD]
[TD="class: xl25, width: 64, bgcolor: white"] [/TD]
[TD="class: xl25, width: 64, bgcolor: white"]Table [/TD]
[TD="class: xl25, width: 64, bgcolor: white"] [/TD]
[TD="class: xl25, width: 64, bgcolor: white"] [/TD]
[TD="class: xl25, width: 64, bgcolor: white"] [/TD]
[/TR]
[TR]
[TD="class: xl25, bgcolor: white"] [/TD]
[TD="class: xl25, bgcolor: white"] [/TD]
[TD="class: xl25, bgcolor: white"] [/TD]
[TD="class: xl25, bgcolor: white"] [/TD]
[TD="class: xl25, bgcolor: white"] [/TD]
[TD="class: xl25, bgcolor: white"] [/TD]
[TD="class: xl25, bgcolor: white"] [/TD]
[TD="class: xl25, bgcolor: white"] [/TD]
[TD="class: xl25, bgcolor: white"] [/TD]
[/TR]
[TR]
[TD="class: xl25, bgcolor: white"]Car Type [/TD]
[TD="class: xl25, bgcolor: white"]Colour [/TD]
[TD="class: xl25, bgcolor: white"]Spend [/TD]
[TD="class: xl25, bgcolor: white"] [/TD]
[TD="class: xl25, bgcolor: white"] [/TD]
[TD="class: xl25, bgcolor: white"]Green[/TD]
[TD="class: xl25, bgcolor: white"]Red[/TD]
[TD="class: xl25, bgcolor: white"]Orange [/TD]
[TD="class: xl25, bgcolor: white"] [/TD]
[/TR]
[TR]
[TD="class: xl26, bgcolor: white"]Car 1[/TD]
[TD="class: xl27, bgcolor: white"]Green[/TD]
[TD="class: xl28, bgcolor: white"]3[/TD]
[TD="class: xl29, bgcolor: white"] [/TD]
[TD="class: xl30, bgcolor: white"]Car 1[/TD]
[TD="class: xl25, bgcolor: white"] [/TD]
[TD="class: xl25, bgcolor: white"] [/TD]
[TD="class: xl25, bgcolor: white"] [/TD]
[TD="class: xl25, bgcolor: white"] [/TD]
[/TR]
[TR]
[TD="class: xl26, bgcolor: white"]Car 2[/TD]
[TD="class: xl27, bgcolor: white"]Red[/TD]
[TD="class: xl28, bgcolor: white"]4[/TD]
[TD="class: xl29, bgcolor: white"] [/TD]
[TD="class: xl30, bgcolor: white"]Car 2[/TD]
[TD="class: xl25, bgcolor: white"] [/TD]
[TD="class: xl25, bgcolor: white"] [/TD]
[TD="class: xl25, bgcolor: white"] [/TD]
[TD="class: xl25, bgcolor: white"] [/TD]
[/TR]
[TR]
[TD="class: xl26, bgcolor: white"]Car 2[/TD]
[TD="class: xl27, bgcolor: white"]Red[/TD]
[TD="class: xl28, bgcolor: white"]2[/TD]
[TD="class: xl29, bgcolor: white"] [/TD]
[TD="class: xl30, bgcolor: white"]Car 3[/TD]
[TD="class: xl25, bgcolor: white"] [/TD]
[TD="class: xl25, bgcolor: white"] [/TD]
[TD="class: xl25, bgcolor: white"] [/TD]
[TD="class: xl25, bgcolor: white"] [/TD]
[/TR]
[TR]
[TD="class: xl26, bgcolor: white"]Car 3[/TD]
[TD="class: xl27, bgcolor: white"]Blue [/TD]
[TD="class: xl28, bgcolor: white"]1[/TD]
[TD="class: xl29, bgcolor: white"] [/TD]
[TD="class: xl30, bgcolor: white"] [/TD]
[TD="class: xl25, bgcolor: white"] [/TD]
[TD="class: xl25, bgcolor: white"] [/TD]
[TD="class: xl25, bgcolor: white"] [/TD]
[TD="class: xl25, bgcolor: white"] [/TD]
[/TR]
[TR]
[TD="class: xl26, bgcolor: white"]Car 1[/TD]
[TD="class: xl27, bgcolor: white"]Orange[/TD]
[TD="class: xl28, bgcolor: white"]2[/TD]
[TD="class: xl29, bgcolor: white"] [/TD]
[TD="class: xl30, bgcolor: white"] [/TD]
[TD="class: xl25, bgcolor: white"] [/TD]
[TD="class: xl25, bgcolor: white"] [/TD]
[TD="class: xl25, bgcolor: white"] [/TD]
[TD="class: xl25, bgcolor: white"] [/TD]
[/TR]
[TR]
[TD="class: xl25, bgcolor: white"] [/TD]
[TD="class: xl25, bgcolor: white"] [/TD]
[TD="class: xl25, bgcolor: white"] [/TD]
[TD="class: xl25, bgcolor: white"] [/TD]
[TD="class: xl25, bgcolor: white"] [/TD]
[TD="class: xl25, bgcolor: white"] [/TD]
[TD="class: xl25, bgcolor: white"] [/TD]
[TD="class: xl25, bgcolor: white"] [/TD]
[TD="class: xl25, bgcolor: white"] [/TD]
[/TR]
</TBODY>[/TABLE]
 

Excel Facts

Add Bullets to Range
Select range. Press Ctrl+1. On Number tab, choose Custom. Type Alt+7 then space then @ sign (using 7 on numeric keypad)
Sort by date Sort by votes
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
 
Upvote 0
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



[TABLE="width: 452"]
<COLGROUP><COL style="WIDTH: 116pt; mso-width-source: userset; mso-width-alt: 5668" width=155><COL style="WIDTH: 48pt" span=7 width=64><TBODY>[TR]
[TD="class: xl26, width: 155, bgcolor: white"]Data [/TD]
[TD="class: xl26, width: 64, bgcolor: white"] [/TD]
[TD="class: xl26, width: 64, bgcolor: white"] [/TD]
[TD="class: xl26, width: 64, bgcolor: white"] [/TD]
[TD="class: xl26, width: 64, bgcolor: white"]Table [/TD]
[TD="class: xl27, width: 64, bgcolor: transparent"][/TD]
[TD="class: xl28, width: 64, bgcolor: white"] [/TD]
[TD="class: xl28, width: 64, bgcolor: white"] [/TD]
[/TR]
[TR]
[TD="class: xl28, bgcolor: white"] [/TD]
[TD="class: xl28, bgcolor: white"] [/TD]
[TD="class: xl28, bgcolor: white"] [/TD]
[TD="class: xl28, bgcolor: white"] [/TD]
[TD="class: xl28, bgcolor: white"] [/TD]
[TD="class: xl28, bgcolor: white"] [/TD]
[TD="class: xl28, bgcolor: white"] [/TD]
[TD="class: xl28, bgcolor: white"] [/TD]
[/TR]
[TR]
[TD="class: xl25, bgcolor: white"]Car Type [/TD]
[TD="class: xl25, bgcolor: white"]Colour [/TD]
[TD="class: xl25, bgcolor: white"]Spend [/TD]
[TD="class: xl28, bgcolor: white"] [/TD]
[TD="class: xl25, bgcolor: white"] [/TD]
[TD="class: xl25, bgcolor: white"]Green[/TD]
[TD="class: xl25, bgcolor: white"]Red[/TD]
[TD="class: xl25, bgcolor: white"]Orange [/TD]
[/TR]
[TR]
[TD="class: xl29, bgcolor: white"]Car 1[/TD]
[TD="class: xl30, bgcolor: white"]Green[/TD]
[TD="class: xl31, bgcolor: white"]3[/TD]
[TD="class: xl32, bgcolor: white"] [/TD]
[TD="class: xl33, bgcolor: white"]Car 1[/TD]
[TD="class: xl28, bgcolor: white"]Spend [/TD]
[TD="class: xl28, bgcolor: white"]Spend [/TD]
[TD="class: xl28, bgcolor: white"]Spend [/TD]
[/TR]
[TR]
[TD="class: xl29, bgcolor: white"]Car 2[/TD]
[TD="class: xl30, bgcolor: white"]Red[/TD]
[TD="class: xl31, bgcolor: white"]4[/TD]
[TD="class: xl32, bgcolor: white"] [/TD]
[TD="class: xl33, bgcolor: white"]Car 2[/TD]
[TD="class: xl28, bgcolor: white"]Spend [/TD]
[TD="class: xl28, bgcolor: white"]Spend [/TD]
[TD="class: xl28, bgcolor: white"]Spend [/TD]
[/TR]
[TR]
[TD="class: xl29, bgcolor: white"]Car 2[/TD]
[TD="class: xl30, bgcolor: white"]Red[/TD]
[TD="class: xl31, bgcolor: white"]2[/TD]
[TD="class: xl32, bgcolor: white"] [/TD]
[TD="class: xl33, bgcolor: white"]Car 3[/TD]
[TD="class: xl28, bgcolor: white"]Spend [/TD]
[TD="class: xl28, bgcolor: white"]Spend [/TD]
[TD="class: xl28, bgcolor: white"]Spend [/TD]
[/TR]
[TR]
[TD="class: xl29, bgcolor: white"]Car 3[/TD]
[TD="class: xl30, bgcolor: white"]Blue [/TD]
[TD="class: xl31, bgcolor: white"]1[/TD]
[TD="class: xl32, bgcolor: white"] [/TD]
[TD="class: xl34, bgcolor: white"] [/TD]
[TD="class: xl28, bgcolor: white"] [/TD]
[TD="class: xl28, bgcolor: white"] [/TD]
[TD="class: xl28, bgcolor: white"] [/TD]
[/TR]
[TR]
[TD="class: xl29, bgcolor: white"]Car 1[/TD]
[TD="class: xl30, bgcolor: white"]Orange[/TD]
[TD="class: xl31, bgcolor: white"]2[/TD]
[TD="class: xl32, bgcolor: white"] [/TD]
[TD="class: xl34, bgcolor: white"] [/TD]
[TD="class: xl28, bgcolor: white"] [/TD]
[TD="class: xl28, bgcolor: white"] [/TD]
[TD="class: xl28, bgcolor: white"] [/TD]
[/TR]
</TBODY>[/TABLE]
 
Upvote 0
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
 
Upvote 0
Re: SumIf - multi variables

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

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

then drag it right and down.
 
Upvote 0
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>
 
Upvote 0
You must log in or register to reply here.

Similar threads

S
  • Solved
Sum from a table if the key matches a variable array
Replies
3
Views
201
Fluff
Fluff
S
  • Question Question
Big, Multi-Step Macro for Efficiently Cutting Big Pieces Into Small Pieces
Replies
1
Views
462
Solon Aquila
S
T
  • Question Question
Sum-Unique - filter
Replies
8
Views
473
Peter_SSs
Peter_SSs
M
  • Question Question
Ranking based on Conditional Formatting colour scheme
Replies
1
Views
2K
awoohaw
awoohaw
Nuke_It_Newport
  • Solved
Conditional Formatting Rules Not Applying
Replies
1
Views
893
Nuke_It_Newport
Nuke_It_Newport

Forum statistics

Threads
1,223,098
Messages
6,170,100
Members
452,301
Latest member
QualityAssurance

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top