Sumif with 3 criteria

Hlatigo

Well-known Member
Joined
Jun 3, 2002
Messages
677
I want to sum a column that may move based on 3 criteria.

This worked great with two crieteria

Code:
=(SUMIF($C$8:$C$223,"EOD - 1",INDEX($F$8:$AB$223,0,MATCH($A286,$F$6:W$6,0)+0)))


this is what i tried to do but get ZERO...and i think i am missing one more variable.
Code:
=(SUMIF($B$8:$B$223,OR($B$8:$B$223="Paper",$B$8:$B$223="Physical"),INDEX($F$8:$AB$223,0,MATCH($A289,$F$6:W$6,0)+0)))



So here are the details.
~I need to sum a column that has a title of "FUELS" which is on $F$6:W$6
~the table is $F$8:$AB$223
~the second variable is on column A and I am search on current month "APR-09" $A8:$A223
~the third criteria is in column B and I want to sum the two rows that has the term "Paper' and Physcial.$B8:$B223

So basically, Sum the two rows that have paper in one and physical in the other that fall under "APR-09" and the data falls under the FUEL coulmn

Hope this makes sense
 

Some videos you may like

Excel Facts

Repeat Last Command
Pressing F4 adds dollar signs when editing a formula. When not editing, F4 repeats last command.

barry houdini

MrExcel MVP
Joined
Mar 23, 2005
Messages
20,825
Try

=SUMPRODUCT(--($B$8:$B$223="APR-09"),--ISNUMBER(MATCH($B$8:$B$223,{"Paper","Physical"},0)),INDEX($F$8:$W$223,0,MATCH($A289,$F$6:W$6,0)))

I'm assuming that column A has text values, not dates, and all rows in column A are populated
 

Hlatigo

Well-known Member
Joined
Jun 3, 2002
Messages
677
If I were smarter...i would have shown what the worksheet looked like...here is a small cut out. thanks for your desire to help.

I changed your formula up a little

=SUMPRODUCT(--($A$8:$A$223=AH92),--ISNUMBER(MATCH($B$8:$B$223,{"Paper","Physical"},0)),INDEX($D$8:$Q$223,0,MATCH($A289,$D$6:Q$6,0)))



******** language=JavaScript src="http://www.interq.or.jp/sun/puremis/colo/popup.js">*********>
kkkkkkk.xls
ABCDEFG
6RiskMonthFUELGASOILGR
7OpenQuantityMTMOpenQuantityMTM
8Feb-08PaperEOD-10.00
9EOD-20
10Change0000
11PhysicalEOD-10000
12EOD-2000$0
13Change0000
14Mar-08PaperEOD-10
15EOD-20
16Change000$0
17PhysicalEOD-10000
18EOD-20000
19Change0000
Data
 
Last edited:

Watch MrExcel Video

Forum statistics

Threads
1,122,193
Messages
5,594,774
Members
413,934
Latest member
austinb

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
Top