• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 139
  • Last Modified:

Add an Argument to this SUMIFS function

Hi Experts:

How would I modify this SUMIFS Function to subtract fields in "HisDataSUM" in which the values in column C were equal to the values in column D?

SUMIFS(HisDataSum!$A:$A,HisDataSum!$B:$B,IF(D13="All","<>0",D13),HisDataSum!$C:$C,IF(D14="All","<>0",D14),HisDataSum!$E:$E,IF(D16="All","<>0",D16) - X,

Using all the same criteria for cells D13, D14, and D16 X needs to Sum the values in HisDataSum column A only in the instances where the values in column C = column D.

If there is an easier way to do this; such as forget about the subtraction and only sum if column C <> column D that will work as well!

Thanks in advance!
0
-Polak
Asked:
-Polak
1 Solution
 
NBVCCommented:
In order to use SUMIFS (and whole column references), you would need to add a helper column which flags when column C is equal to column C.... for example =IF(C2=D2,"Match","Not Matched")

Then you can add that check in your original.

e.g.

SUMIFS(HisDataSum!$A:$A,HisDataSum!$B:$B,IF(D13="All","<>0",D13),HisDataSum!$C:$C,IF(D14="All","<>0",D14),HisDataSum!$E:$E,IF(D16="All","<>0",D16,HisDataSum!$X:$X,"<>Match")

where X:X is flag column used...

Otherwise, you would need to use SUMPRODUCT and limit range sizes.
0
 
-PolakAuthor Commented:
Flag column created, and it works, not the most elegant, but certainly better than limiting range sizes!
0

Featured Post

Upgrade your Question Security!

Your question, your audience. Choose who sees your identity—and your question—with question security.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now