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!

 

[Eric W]

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]

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:

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

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]

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:

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

Does this layout look anything like your sheet?

	A	W	Z	AC	AD	AE	CC	CD	CE	CF	DU	DV	DW	DX
1	47
2
3
4
5
6
7														dx7
8
9			1	dv15
10			2
11		dx7	3
12			4		dv15
13			5					dv15
14			6
15			7	dv15	dv15			dv15		dv15		dw15
16			8
17			9			dv15
18			10
19
20

<tbody>
</tbody>

Sheet12​

Array Formulas Cell Formula 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]

Does this layout look anything like your sheet?

	A	W	Z	AC	AD	AE	CC	CD	CE	CF	DU	DV	DW	DX
1	47
2
3
4
5
6
7														dx7
8
9			1	dv15
10			2
11		dx7	3
12			4		dv15
13			5					dv15
14			6
15			7	dv15	dv15			dv15		dv15		dw15
16			8
17			9			dv15
18			10
19
20

<tbody>
</tbody>

Sheet12​

Array Formulas Cell Formula 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]

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]

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.

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]

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?