SUMIFS With Multiple Non Contiguous Columns

G

G00DNESS

Board Regular
Joined
Aug 6, 2014
Messages
60
This test formula works correctly USING ONLY 1 COLUMN: AC9:AC208
Code: 
=SUMIFS(Z9:Z208,AC9:AC208,DV15)

However, I have 2 sets of columns non contiguous: AC9:AW208 and CD9:CX208

This is an idea of what I'm trying to achieve:
Code: 
=SUMIFS(Z9:Z208,[COLOR=#ff0000]AC9:AW208[/COLOR],DV15) + SUMIFS(Z9:Z208,[COLOR=#ff0000]CD9:CX208[/COLOR],DV15)

Much thanks for any help with this!
 
Sort by date Sort by votes
Okay I think I see where the problem is. And this may answer your questions.


Each array has separate corresponding Columns:
AC:AW corresponds with both W and Z.
CD:CX corresponds with both CA and BX


If '5' is entered anywhere in AC9:AW208, the corresponding amounts to be applied would be in Column W.
If '5' is entered anywhere in CD9:CX208, the corresponding amounts to be applied would be in Column BZ.


Your question, "What if AC10 is '5' and AD10 is '5" ...
Answer: There will be no duplicate numbers on the same row within AC9:AW208. Likewise, there will be no duplicate numbers on the same row within CD9:CX208.


Your question, "What if AC10 is empty and AD10 is '5'...
Answer: In such case, if W10 had an amount in it, it would count. So if W10 had 50, 50 would be a counted sum for the AC9:AW208 array.
 
Upvote 0

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.
I still have a few questions, but consider this:


WZACADAEBXCACCCDCECFDUDVDWDXDY
text

<tbody>
[TD="align: center"]7[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"]1[/TD]

[TD="align: center"]8[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]9[/TD]
[TD="align: right"]3[/TD]
[TD="align: right"]50[/TD]
[TD="align: right"]5[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]50[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]10[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]50[/TD]
[TD="align: right"]5[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]50[/TD]
[TD="align: right"][/TD]
[TD="align: right"]5[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]11[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]300[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]300[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]12[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]100[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]100[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]13[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]14[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]15[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"]5[/TD]

[TD="align: right"]100[/TD]
[TD="align: right"][/TD]

</tbody>
Sheet12​

[TABLE="width: 85%"]
<tbody>[TR]
[TD]Array Formulas[TABLE="width: 100%"]
<tbody>[TR="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]
[TH]Cell[/TH]
[TH="align: left"]Formula[/TH]
[/TR]
[TR]
[TH="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]DX15[/TH]
[TD="align: left"]{=IF(OR(DW15="",DW15="N/A",AND(COUNTIF(W9:W208,DX7)=0,COUNTIF(BX9:BX208,DX7)=0)),"",SUM((Z9:Z208+CA9:CA208)*SIGN((MMULT(--(AC9:AW208=DV15),TRANSPOSE(COLUMN(AC9:AW208)))*MMULT(--(CD9:CX208=DV15),TRANSPOSE(COLUMN(CD9:CX208)))))))}[/TD]
[/TR]
</tbody>[/TABLE]
Entered with Ctrl+Shift+Enter. If entered correctly, Excel will surround with curly braces {}.
Note: Do not try and enter the {} manually yourself[/TD]
[/TR]
</tbody>[/TABLE]





I don't understand the relationship between columns W/Z and BX/CA. Does W have to have something in it for that row to be counted? Does it need to match DX7? Right now the formula ignores W/BX, except if the whole range is empty it will return "".
 
Upvote 0
The relationship is the [for the 'General Transaction' Section of the Worksheet], when a code is populated in Column W that matches the code in DV7, a figure (Dollars) is generated in Column Z.
The relationship is the [for the 'Specialized Transaction' Section of the Worksheet], when a code is populated in Column BX that matches the code in DV7, and a figure (Dollars) is generated in Column CA.
I'm trying to get the combined dollar amounts for each code that matches DV7.


I made a helper table for this issue in cells EJ15, EK15, EL15. It works, but it is a bit much considering that the formula I need will be used in other cells. It might be easier to simplify than the other way. Not sure.


Cell EJ15:
Code: 
=IF(OR(DW15="",DW15="N/A",COUNTIF(W9:W208,DX7)=0),"",SUMIFS(Z9:Z208,AC9:AC208,DV15)+SUMIFS(Z9:Z208,AD9:AD208,DV15)+SUMIFS(Z9:Z208,AE9:AE208,DV15)+SUMIFS(Z9:Z208,AF9:AF208,DV15)+SUMIFS(Z9:Z208,AG9:AG208,DV15)+SUMIFS(Z9:Z208,AH9:AH208,DV15)+SUMIFS(Z9:Z208,AI9:AI208,DV15)+SUMIFS(Z9:Z208,AJ9:AJ208,DV15)+SUMIFS(Z9:Z208,AK9:AK208,DV15)+SUMIFS(Z9:Z208,AL9:AL208,DV15)+SUMIFS(Z9:Z208,AM9:AM208,DV15)+SUMIFS(Z9:Z208,AN9:AN208,DV15)+SUMIFS(Z9:Z208,AO9:AO208,DV15)+SUMIFS(Z9:Z208,AP9:AP208,DV15)+SUMIFS(Z9:Z208,AQ9:AQ208,DV15)+SUMIFS(Z9:Z208,AR9:AR208,DV15)+SUMIFS(Z9:Z208,AS9:AS208,DV15)+SUMIFS(Z9:Z208,AT9:AT208,DV15)+SUMIFS(Z9:Z208,AU9:AU208,DV15)+SUMIFS(Z9:Z208,AV9:AV208,DV15)+SUMIFS(Z9:Z208,AW9:AW208,DV15))

Cell EK15:
Code: 
=IF(OR(DW15="",DW15="N/A",COUNTIF(BX9:BX208,DX7)=0),"",SUMIFS(CA9:CA208,CD9:CD208,DV15)+SUMIFS(CA9:CA208,CE9:CE208,DV15)+SUMIFS(CA9:CA208,CF9:CF208,DV15)+SUMIFS(CA9:CA208,CG9:CG208,DV15)+SUMIFS(CA9:CA208,CH9:CH208,DV15)+SUMIFS(CA9:CA208,CI9:CI208,DV15)+SUMIFS(CA9:CA208,CJ9:CJ208,DV15)+SUMIFS(CA9:CA208,CK9:CK208,DV15)+SUMIFS(CA9:CA208,CL9:CL208,DV15)+SUMIFS(CA9:CA208,CM9:CM208,DV15)+SUMIFS(CA9:CA208,CN9:CN208,DV15)+SUMIFS(CA9:CA208,CO9:CO208,DV15)+SUMIFS(CA9:CA208,CP9:CP208,DV15)+SUMIFS(CA9:CA208,CQ9:CQ208,DV15)+SUMIFS(CA9:CA208,CR9:CR208,DV15)+SUMIFS(CA9:CA208,CS9:CS208,DV15)+SUMIFS(CA9:CA208,CT9:CT208,DV15)+SUMIFS(CA9:CA208,CU9:CU208,DV15)+SUMIFS(CA9:CA208,CV9:CV208,DV15)+SUMIFS(CA9:CA208,CW9:CW208,DV15)+SUMIFS(CA9:CA208,CX9:CX208,DV15))

Cell EK15: (The total for both cells--what I'm trying to achieve)
Code: 
=EJ16+EK16
 
Upvote 0
As I understand it, then I think my latest formula does what you want. However, it seems that your 3 formula solution will add the totals separately, and won't require DV15 to show up in both tables on a given row? That's a difference.

It also occurs to me that if you're willing to use a helper column, you can get a much simpler formula. Let's say you have column EA free. Put this in EA9 and drag down to EA208:

=COUNTIF(AC10:AW10,$DV$15)*COUNTIF(CD10:CX10,$DV$15)*(Z10+CA10)

Hide the column if you want. Now the formula for DX15 is just:

=IF(OR(DW15="",DW15="N/A"),"",SUM(EA9:EA208))
 
Upvote 0
You must log in or register to reply here.

Similar threads

M
  • Question Question
Direct Referencing Non Contiguous Columns
Replies
2
Views
104
Fuji
F
J
  • Question Question
Dynamic month selection to SUMIF (Tried XLOOKUP)
Replies
2
Views
100
djclements
djclements
M
  • Question Question
Help to SUMIF past 12 months
Replies
3
Views
163
RoryA
RoryA
S
  • Question Question
SUMIF and Search
Replies
2
Views
407
Super Mario
S
U
  • Solved
SumIf formula with multiple criteria from different tab
Replies
3
Views
510
Alex Blakenburg
A

Forum statistics

Threads
1,224,823
Messages
6,181,181
Members
453,022
Latest member
Mohamed Magdi Tawfiq Emam

Share this page

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