Sumifs with multiple cell references as criteria

[spoony40]

Hi,

I can get this formula to work:

=SUMIFS(A:A, B:B, D1, C:C, D2:D3)

It uses criteria in cell D2 and D3. But how do i get it to reference a cell not in order like D2 OR D10

=SUMIFS(A:A, B:B, D1, C:C, (D2,D10)) gives zero value and not working.

 

[AlphaFrog]

Try this...

=SUM(SUMIFS(A:A, B:B, D1, C:C, {D2,D10}))

 

[spoony40]

Try this...

=SUM(SUMIFS(A:A, B:B, D1, C:C, {D2,D10}))

It gives an error. Is it the correct syntax normally?

Ah it looks like my original formula has problems too...

 

[AlphaFrog]

Do you have any formula errors in columns A, B, or C?

 

[spoony40]

My original formula is not working:

=SUMIFS(A:A, B:B, D1, C:C, D2:D3)

It only takes D3 as the criteria not D2. When i use your formula, theres a popup saying error "There's a problem with this formula etc"

 

[spoony40]

Do you have any formula errors in columns A, B, or C?

No formulas in columns, its all raw data.

=SUMIFS(A:A,B:B,D1,C:C,D2) = 5

=SUM(SUMIFS(A:A,B:B,D1,C:C,{D2,D3})) = gives popup error

[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD]12[/TD]
[TD]a[/TD]
[TD]a[/TD]
[TD]a[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]a[/TD]
[TD]b[/TD]
[TD]b[/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]a[/TD]
[TD]c[/TD]
[TD]c[/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD]a[/TD]
[TD]d[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]a[/TD]
[TD]b[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]5[/TD]
[TD]a[/TD]
[TD]a[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]a[/TD]
[TD]c[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]d[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

 

[AhoyNC]

Try:
The two SUMIFS will probably process quicker if using whole columns as a range. You would be better off to limit your ranges to something like A1:A5000.

Excel Workbook
	A	B	C	D	E	F
1	12	a	a	a
2	2	a	b	b		10
3	3	a	c	c
4	4	a	d			10
5	3	a	b
6	5	a	a
7	2	a	c
8	1	d
Sheet

 

[spoony40]

ok thanks, i'll use that for now till i can figure out how to use an array.

 

[Aladin Akyurek]

Try...

=SUMPRODUCT(SUMIFS(A:A, B:B, D1, C:C, D2:D3))