SUMIFS Multiple Criteria - Same Column

neodjandre

Well-known Member
Joined
Nov 29, 2006
Messages
950
Office Version
  1. 2019
Platform
  1. Windows
I am using this formula

SUMIFS(BankFeed1[Calc],BankFeed1[Date],">=" &v_start,BankFeed1[Date],"<=" &v_end,BankFeed1[Cstat],{name1,name2})

But only name1 gets summed up. I thought by using curly brackets, I could sum up two criteria in same column.
 

Excel Facts

Which lookup functions find a value equal or greater than the lookup value?
MATCH uses -1 to find larger value (lookup table must be sorted ZA). XLOOKUP uses 1 to find values greater and does not need to be sorted.
You will need to wrap the whole formula in sum
=SUM(your formula)
 
Upvote 0
hmm this works, but what if name1 is a named range? I get an error when I put the named range in the curly brackets
 
Upvote 0
As far as I know you cannot use ranges, or named ranges inside an array like that.
 
Upvote 0
hmm this works, but what if name1 is a named range? I get an error when I put the named range in the curly brackets

Maybe this...

=SUMPRODUCT(SUMIFS(BankFeed1[Calc],BankFeed1[Date],">=" &v_start,BankFeed1[Date],"<=" &v_end,BankFeed1[Cstat],CHOOSE({1,2},name1,name2)))

M.
 
Upvote 0
Marcelo, I have tested this and it only picks up first option, name1
 
Upvote 0
Marcelo, I have tested this and it only picks up first option, name1

Have you changed SUM by SUMPRODUCT?

Worked perfectly for me.

See if this example helps:

A
B
C
D
E
F
G
H
1
Name​
Score​
Sum of A and J​
A​
J​
M​
2
Anthony​
10​
43​
Anthony​
John​
Mary​
3
Albert​
9​
Albert​
Jacob​
Mike​
4
John​
8​
James​
5
Jacob​
9​
6
James​
7​
7
Mary​
10​
8
Mike​
9​
9

<tbody>
</tbody>


Named ranges
A --> F2:F3
J --> G2:G4
M --> H2:H3

Formula in D2
=SUMPRODUCT(SUMIF(A2:A8,CHOOSE({1,2},A,J),B2:B8))

As you can see using SUMPRODUCT and CHOOSE worked!

M.
 
Upvote 0
@Marcelo

The only thing you have to be careful about with such constructions, in which the two Named Ranges (A and J) are of different dimensions, is that any #N/A errors within the range will equally be considered for summation. For example, using your example change the entry in A7 from Mary to =NA(). The result is now 53.

This is because, in this case, CHOOSE redimensions the smaller of the two ranges such that it is of a dimension equal to the larger, filling in any 'missing' entries with #N/A errors, i.e.:

CHOOSE({1,2},A,J)

evaluates to:

{"Anthony","John";"Albert","Jacob";#N/A,"James"}

and that #N/A is (strangely, perhaps) a perfectly legitimate value to be summed, should it be present within the range (unlikely as that may be in practice).

Cheers
 
Upvote 0
XOR

I knew this risk, but I was not expecting #N/A in column A.

Possible solutions
1. Make all ranges the same size (a blank cell is evaluated as 0 - no problem)

2. A workaround
=SUMPRODUCT(SUMIFS(B2:B8,A2:A8,CHOOSE({1,2},A,J),A2:A8,"?*"))

M.
 
Upvote 0

Forum statistics

Threads
1,213,530
Messages
6,114,162
Members
448,554
Latest member
Gleisner2

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