sumif with three HLOOKUPs?

[r_simpson42]

[TABLE="width: 495"]
<TBODY>[TR]
[TD]Account</SPAN>
[/TD]
[TD]DocumentNo</SPAN>
[/TD]
[TD]Type</SPAN>
[/TD]
[TD]Text</SPAN>
[/TD]
[TD]Amount in doc. curr.</SPAN>
[/TD]
[/TR]
[TR]
[TD]504520</SPAN>
[/TD]
[TD]1</SPAN>
[/TD]
[TD]AD</SPAN>
[/TD]
[TD]description1</SPAN>
[/TD]
[TD]-55.00</SPAN>
[/TD]
[/TR]
[TR]
[TD]504530</SPAN>
[/TD]
[TD]2</SPAN>
[/TD]
[TD]</SPAN>XX
[/TD]
[TD]description2</SPAN>
[/TD]
[TD]86.00</SPAN>
[/TD]
[/TR]
[TR]
[TD]504540</SPAN>
[/TD]
[TD]3</SPAN>
[/TD]
[TD]</SPAN>XX
[/TD]
[TD]description3</SPAN>
[/TD]
[TD]9,000.00</SPAN>
[/TD]
[/TR]
[TR]
[TD]504520</SPAN>
[/TD]
[TD]4</SPAN>
[/TD]
[TD]AD</SPAN>
[/TD]
[TD]description4</SPAN>
[/TD]
[TD]100,000.00</SPAN>
[/TD]
[/TR]
[TR]
[TD]504580</SPAN>
[/TD]
[TD]5</SPAN>
[/TD]
[TD]AD</SPAN>
[/TD]
[TD]description5</SPAN>
[/TD]
[TD]-8,500.00</SPAN>
[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</TBODY>[/TABLE]
The system that generates the above table does not always deliver the columns in the same order. I need to sumif based on the following criteria:
Find column "Account"
Find column "Type"
Find column "Amount in doc. curr."
Sum amounts in column "Amount in doc. curr." If "Account" = 504520 and "Type" = AD (Should equal 99,945)

 

[MFitz10]

This should work:

=SUMIFS($E$2:$E$6,$A$2:$A$6,H2,$C$2:$C$6,I2)

H2 = 504520
I2 = AD

 

[Aladin Akyurek]

G1: Account
G2: 504520

H1: Type
H2: AD

I1: Amount in doc. curr.
I2, just enter and copy down:

=SUMIFS(
    INDEX($A$2:$E$6,0,MATCH($I$1,$A$1:$E$1,0)),
    INDEX($A$2:$E$6,0,MATCH($G$1,$A$1:$E$1,0)),$G2,
    INDEX($A$2:$E$6,0,MATCH($H$1,$A$1:$E$1,0)),$H2)

 

[Jonmo1]

Try

=SUMIFS(INDEX(A2:Z100,0,MATCH("Amount in doc. curr.",A1:Z1,0)),INDEX(A2:Z100,0,MATCH("Account",A1:Z1,0)),504520,INDEX(A2:Z100,0,MATCH("Type",A1:Z1,0)),"AD")


Adjust A2:Z100 to a size large enough to accomodate all possibilities.
Also, the A1:Z1 should be adjusted to the same number of columns used in A2:Z100

 

[r_simpson42]

Thank you for the help! This was what I needed!

 

[Aladin Akyurek]

Thank you for the help! This was what I needed!

You are welcome.

 

[proficient]

G1: Account
G2: 504520

H1: Type
H2: AD



I1: Amount in doc. curr.
I2, just enter and copy down:

=SUMIFS(
    INDEX($A$2:$E$6,0,MATCH($I$1,$A$1:$E$1,0)),
    INDEX($A$2:$E$6,0,MATCH($G$1,$A$1:$E$1,0)),$G2,
    INDEX($A$2:$E$6,0,MATCH($H$1,$A$1:$E$1,0)),$H2)

Aladin Sir,

Can't the formula work without 0...?

 

[Aladin Akyurek]

Aladin Sir,

Can't the formula work without 0...?

No.

The 0 in

INDEX($A$2:$E$6,0,MATCH($I$1,$A$1:$E$1,0))

means "all cells/rows" of the column (of A2:E6) determined by the MATCH bit.

 

[proficient]

No.

The 0 in

INDEX($A$2:$E$6,0,MATCH($I$1,$A$1:$E$1,0))

means "all cells/rows" of the column (of A2:E6) determined by the MATCH bit.

Thank you very much for the explanation...

 

[Aladin Akyurek]

Thank you very much for the explanation...

You are welcome.