Excel SUMIFS with multiple AND criterias in same column

[aiyathomas]

Hi,

I have a table structure below, and want to calculate the sub-total with the criteria of Server A "AND" Server B" in Subscription 1 (excluding "Server C"), I tried both of the below formula, but it doesn't works. Please can someone help? Thanks a lot.

L3 = Table Name

=SUMIFS(INDIRECT(L$3&"[[#All],[Cost]]"),INDIRECT(L$3&"[[#All],[Subscription]]"),"Subscription 1",INDIRECT(L$3&"[[#All],[Server]]"), "Server A", INDIRECT(L$3&"[[#All],[Server]]"), "Server B")

=SUMIFS(INDIRECT(L$3&"[[#All],[Cost]]"),INDIRECT(L$3&"[[#All],[Subscription]]"),"Subscription 1",INDIRECT(L$3&"[[#All],[Server]]"), {"Server A", "Server B"})

Subscription	Server	Cost
Subscription 1	Server A	US 25.4
Subscription 1	Server A	US 13.9
Subscription 2	Server D	US 20.2
Subscription 1	Server A	US 6.7
Subscription 1	Server B	US 33.5
Subscription 2	Server E	US 16.8
Subscription 1	Server B	US 11.4
Subscription 1	Server C	US 27.8

[shukla.ankur281190]

Attach a sample workbook. Make sure there is just enough data to demonstrate your need. Include a BEFORE sheet and an AFTER sheet in the workbook if needed to show the process you're trying to complete or automate. Make sure your desired results are shown, mock them up manually if necessary.

Remember to desensitize the data.

Click on GO ADVANCED and then scroll down to Manage Attachments to open the upload window.

[sandy666]

try: =SUMIFS($C$2:$C$9,$A$2:$A$9,"Subscription 1",$B$2:$B$9,"Server A")+SUMIFS($C$2:$C$9,$A$2:$A$9,"Subscription 1",$B$2:$B$9,"Server B")

[FlameRetired]

This modification to sandy's formula should work, too.

=SUMPRODUCT(SUMIFS($C$2:$C$9,$A$2:$A$9,"Subscription 1",$B$2:$B$9,{"Server A","Server B"}))

[sandy666]

Thanks Dave but I think it will be enough:
=SUM(SUMIFS($C$2:$C$9,$A$2:$A$9,"Subscription 1",$B$2:$B$9,{"Server A","Server B"}))

[aiyathomas]

Thanks Ankur Shukla & Sandy.

@Sandy, any other ways to do as it will be very long since I have around 1x different servers and will add more in the future. I have created a separate table contains "Server A" & "Server B", is that able to utilize in the formula? Thanks.

Best regards,
Thomas Lam

[FlameRetired]

@ sandy

Good point. I hadn't even tried that... I just went right to the "big" guns. LOL

[FDibbins]

Try this. I put your criteria in their own cells and referenced them, this makes it a bit more dynamic. You could, of course, hard code the names instead.

	A	B	C	D	E
1					Server A
2	Subscription	Server	Cost		server B
3	Subscription 1	Server A	25.4	90.9	Subscription 1
4	Subscription 1	Server A	13.9
5	Subscription 2	Server D	20.2
6	Subscription 1	Server A	6.7
7	Subscription 1	Server B	33.5
8	Subscription 2	Server E	16.8
9	Subscription 1	Server B	11.4
10	Subscription 1	Server C	27.8

D3=SUMPRODUCT(--($A$3:$A$10=$E$3),--($B$3:$B$10=$E$1)+($B$3:$B$10=E2),C3:C10)

[sandy666]

You can use formula from post #4 or #5, something like:

=SUM(SUMIFS($C$2:$C$9,$A$2:$A$9,"Subscription 1",$B$2:$B$9,{"Server A","Server B","other1","other2","etc..."}))
or
SUMPRODUCT(SUMIFS($C$2:$C$9,$A$2:$A$9,"Subscription 1",$B$2:$B$9,$F$1:$F$10))
where F1:F10 is a list of servers

[aiyathomas]

BIG Thanks to everyone, and below is perfectly works on my case. Much appreciated. Thanks Sandy.

SUMPRODUCT(SUMIFS($C$2:$C$9,$A$2:$A$9,"Subscription 1",$B$2:$B$9,$F$1:$F$10))
where F1:F10 is a list of servers

Best regards,
Thomas Lam

[sandy666]

Glad to help

If it works for you read dancing text in my footnote (here or above)

[sandy666]

Btw, if there is more Subcriptions you can do the same as for Servers

[aiyathomas]

Done Sandy

[sandy666]

Not yet
Scroll up, top right corner, Thread Tools, Mark Thread SOLVED