SUMIFS With Multiple Non Contiguous Columns

G00DNESS

Board Regular
Joined
Aug 6, 2014
Messages
56
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!
 

Some videos you may like

Excel Facts

When did Power Query debut in Excel?
Although it was an add-in in Excel 2010 & Excel 2013, Power Query became a part of Excel in 2016, in Data, Get & Transform Data.

Eric W

MrExcel MVP
Joined
Aug 18, 2015
Messages
9,767
On a SUMIFS, all the ranges must be the same size, which is not the case in your example. Let's look at one line. If your DV15 value is found in AC9, you want to add Z9 to the total, right? What if DV15 is found in AC9 and AD9? Add Z9 once or twice? What if it's in AC9, AD9, and CE9? 1, 2, or 3 times? If the answer is add in Z9 if there are 1 or more matches in Z9:AC9 or CD9:CX9, then this array formula might work:

=SUM(Z9:Z208*(MMULT(--(AC9:AW208=DV15),TRANSPOSE(COLUMN(AC9:AW208)))+MMULT(--(CD9:CX208=DV15),TRANSPOSE(COLUMN(CD9:CX208)))>0))
confirmed with Control+Shift+Enter.
 

G00DNESS

Board Regular
Joined
Aug 6, 2014
Messages
56
On a SUMIFS, all the ranges must be the same size, which is not the case in your example. Let's look at one line. If your DV15 value is found in AC9, you want to add Z9 to the total, right? What if DV15 is found in AC9 and AD9? Add Z9 once or twice? What if it's in AC9, AD9, and CE9? 1, 2, or 3 times? If the answer is add in Z9 if there are 1 or more matches in Z9:AC9 or CD9:CX9, then this array formula might work:

=SUM(Z9:Z208*(MMULT(--(AC9:AW208=DV15),TRANSPOSE(COLUMN(AC9:AW208)))+MMULT(--(CD9:CX208=DV15),TRANSPOSE(COLUMN(CD9:CX208)))>0))
confirmed with Control+Shift+Enter.
I get an error message. My apologies! I should have added the beginning segment of the formula since it makes a difference.
This is what I am trying to acheive:


Code:
=IF(OR(DW15="",DW15="N/A",COUNTIF(W9:W208,DX7)=0),"",SUMIFS(Z9:Z208,AC9:AW208,DV15) + SUMIFS(Z9:Z208,CD9:CX208,DV15)
 

Eric W

MrExcel MVP
Joined
Aug 18, 2015
Messages
9,767
The beginning part of the formula shouldn't make a difference, just add it to the beginning of the formula I provided.

What error message are you getting?

You didn't answer any of my questions. Showing a non-working formula really doesn't tell me what you want it to do. Should I continue to assume that if DV15 appears anywhere on a row, you want to add the Z value once? Can DV15 occur in multiple places on a row?
 

G00DNESS

Board Regular
Joined
Aug 6, 2014
Messages
56
The beginning part of the formula shouldn't make a difference, just add it to the beginning of the formula I provided.

What error message are you getting?

You didn't answer any of my questions. Showing a non-working formula really doesn't tell me what you want it to do. Should I continue to assume that if DV15 appears anywhere on a row, you want to add the Z value once? Can DV15 occur in multiple places on a row?
Answer: Yes, if DV15 appears anywhere on a row
Answer: Yes, I want the total of ALL Z values that match
Answer: Yes, DV15 occur in multiple places on a row
And there may be blank cells in the columns

I used:
Code:
=IF(OR(DW15="",DW15="N/A",COUNTIF(W9:W208,DX7)=0),"",SUM(Z9:Z208*(MMULT(--(AC9:AW208=DV15),TRANSPOSE(COLUMN(AC9:AW208)))+MMULT(--(CD9:CX208=DV15),TRANSPOSE(COLUMN(CD9:CX208)))>0)))
Error: A vlaue used in this formula is of the wrong data type
 

Eric W

MrExcel MVP
Joined
Aug 18, 2015
Messages
9,767
Does this layout look anything like your sheet?

AWZACADAECCCDCECFDUDVDWDX
147
2
3
4
5
6
7dx7
8
91dv15
102
11dx73
124dv15
135dv15
146
157dv15dv15dv15dv15dw15
168
179dv15
1810
19
20

<tbody>
</tbody>
Sheet12

Array Formulas
CellFormula
A1{=IF(OR(DW15="",DW15="N/A",COUNTIF(W9:W208,DX7)=0),"",SUM(MMULT(--(AC9:AW208=DV15),TRANSPOSE(COLUMN(AC9:AW208))^0)+MMULT(--(CD9:CX208=DV15),TRANSPOSE(COLUMN(CD9:CX208))^0))))}

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



The formula is in A1, the other ranges are just filled in with data based on what I can infer from the formula. It returns 47, which is calculated as follows:

DV15 occurs 1 time in row 9, times z9 = 1
DV15 occurs 1 time in row 12, times z12 = 4
DV15 occurs 1 time in row 13, times z13 = 5
DV15 occurs 4 times in row 15, times z15 = 4 * 7 = 28
DV15 occurs 1 time in row 17, times z17 = 9

1+4+5+28+9=47

How well does this work for you?
 

G00DNESS

Board Regular
Joined
Aug 6, 2014
Messages
56
Does this layout look anything like your sheet?

AWZACADAECCCDCECFDUDVDWDX
147
2
3
4
5
6
7dx7
8
91dv15
102
11dx73
124dv15
135dv15
146
157dv15dv15dv15dv15dw15
168
179dv15
1810
19
20

<tbody>
</tbody>
Sheet12

Array Formulas
CellFormula
A1{=IF(OR(DW15="",DW15="N/A",COUNTIF(W9:W208,DX7)=0),"",SUM(MMULT(--(AC9:AW208=DV15),TRANSPOSE(COLUMN(AC9:AW208))^0)+MMULT(--(CD9:CX208=DV15),TRANSPOSE(COLUMN(CD9:CX208))^0))))}

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



The formula is in A1, the other ranges are just filled in with data based on what I can infer from the formula. It returns 47, which is calculated as follows:

DV15 occurs 1 time in row 9, times z9 = 1
DV15 occurs 1 time in row 12, times z12 = 4
DV15 occurs 1 time in row 13, times z13 = 5
DV15 occurs 4 times in row 15, times z15 = 4 * 7 = 28
DV15 occurs 1 time in row 17, times z17 = 9

1+4+5+28+9=47

How well does this work for you?
That returns the number of times DV15 is counted.
I'm trying to get the corresponding total sum of Columns Z and CA.
Columns Z and CA have dollar amounts, and I'm trying to get the total where DV15 appears in AC9:AW208 andCD9:CX208
 

Eric W

MrExcel MVP
Joined
Aug 18, 2015
Messages
9,767
I'm getting a little frustrated trying to understand what you want. At no time have you ever mentioned column CA until now. Do you want the number of times DV15 occurs in AC:AW times Z, PLUS the number of times DV15 occurs in CD:CX times CA, repeated for each row in 9:208?
 

G00DNESS

Board Regular
Joined
Aug 6, 2014
Messages
56
No. I'm sorry for your frustration. I am grateful for your help.
I've modified the formula conditions, but cannot do it with the arrays.
Here is the scenario:

FROM WITHIN CELL DX15
CRITERIA: IF(OR(DW15="",DW15="N/A","",
CRITERIA:IF(AND(COUNTIF(W9:W208,DX7)=0,COUNTIF(BX9:BX208,DX7)=0),"",
RETURN THE SUM FOUND BOTH COLUMNS 'Z' AND 'CA' WHEN DV15 IS A MATCH IN BOTH ARRAYS 'AC:AW' + 'CD:CX'

I guess it is not possible to send you the Workbook file, and I'm having problems trying to create pics that include the worksheet data headings. :mad:

The cell values are:

DV15 is 5
DW15 is the word TEXT
DX7 is 1
BX9:BX12 each have the cell value 1
W9 is 3
W10:12 is 1 in each cell
Z9 is 50
Z10 is 50
Z11 is 300
Z12 is 100
CA9 is 50
CA10 is 50
CA11 is 300
CA12 is 100
AC9 is 5
AC10 is 5
CD10 is 5

The results for the formula in DX15 should equal 100, which represents the total amount of times where DV15 appeared in both Columns AC and CD, and the corresponding amounts for each time matched in both Columns Z and CA are (50 in Z10 + 50 in CA10). The total amount that should appear in Cell DX15 should return 100.
 

Eric W

MrExcel MVP
Joined
Aug 18, 2015
Messages
9,767
I think I see the basic process. A few followup questions:

What if CD9 is 5? Do I add (50+50) to the total, even though W9 is not 1?

What if AC10 is 5 and AD10 is 5? Do I add an extra 50 from Z10, or does the amount stay the same?

What if AC10 is empty and AD10 is 5? Same answer as your example?
 

Watch MrExcel Video

Forum statistics

Threads
1,099,372
Messages
5,468,227
Members
406,574
Latest member
HeinrichPaul

This Week's Hot Topics

Top