SUMIFS With Multiple Non Contiguous Columns

[G00DNESS]

This test formula works correctly USING ONLY 1 COLUMN: AC9:AC208

=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:

=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!

 

[G00DNESS]

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.

 

[Eric W]

I still have a few questions, but consider this:

	W	Z	AC	AD	AE	BX	CA	CC	CD	CE	CF	DU	DV	DW	DX	DY
7															1
8
9	3	50	5			1	50
10	1	50	5			1	50		5
11	1	300				1	300
12	1	100				1	100
13
14
15													5	text	100

<tbody>
</tbody>

Sheet12​

Array Formulas Cell Formula DX15 {=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)))))))} <tbody> </tbody> Entered with Ctrl+Shift+Enter. If entered correctly, Excel will surround with curly braces {}. Note: Do not try and enter the {} manually yourself

<tbody>
</tbody>

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 "".

 

[G00DNESS]

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:

=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:

=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)

=EJ16+EK16

 

[Eric W]

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))