Sumif on Non-Continuous Range

[acool]

Hi Everyone,

I am currently trying to create a sumif formula that sums the the first twelve months where the value is NOT equal to 0 in the non-continuous range of A2:M2-O2:Z2. I have provided an example of what the outputs should look like in column AB. As mentioned the Range is non-continuous and I would like to sum the first 12 Non Zero Values in the range above.

Thank You!

 

[awoohaw]

Try something like this:

But not really sure what mean by non contiguous ranges and first 12 non zero values? Please clarify.

Mr Excel Questions 72.xlsm
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T	U	V	W	X	Y	Z	AA	AB
1	2024-01-01	2024-02-01	2024-03-01	2024-04-01	2024-05-01	2024-06-01	2024-07-01	2024-08-01	2024-09-01	2024-10-01	2024-11-01	2024-12-01	Total	Year	2025-01-01	2025-02-01	2025-03-01	2025-04-01	2025-05-01	2025-06-01	2025-07-01	2025-08-01	2025-09-01	2025-10-01	2025-11-01	2025-12-01	Total	Grand Total
2	10	10	3	7	2	7	3	8	5	10	1	7		2024	4	8	4	8	8	2	3	6	1	3	0	7		127
3	3	3	7	6	7	4	0	6	1	8	7	0		2024	7	9	8	6	1	2	2	10	4	8	7	9		125
acool

Cell Formulas
Range	Formula
AB2:AB3	AB2	=SUM(($A2:$AA2)*(--(ISNUMBER($A$1:$AA$1))))

I used my own number values, I'm not going to type all the exact information. But, the formula should work.

 

[acool]

Try something like this:

But not really sure what mean by non contiguous ranges and first 12 non zero values? Please clarify.

Mr Excel Questions 72.xlsm
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T	U	V	W	X	Y	Z	AA	AB
1	2024-01-01	2024-02-01	2024-03-01	2024-04-01	2024-05-01	2024-06-01	2024-07-01	2024-08-01	2024-09-01	2024-10-01	2024-11-01	2024-12-01	Total	Year	2025-01-01	2025-02-01	2025-03-01	2025-04-01	2025-05-01	2025-06-01	2025-07-01	2025-08-01	2025-09-01	2025-10-01	2025-11-01	2025-12-01	Total	Grand Total
2	10	10	3	7	2	7	3	8	5	10	1	7		2024	4	8	4	8	8	2	3	6	1	3	0	7		127
3	3	3	7	6	7	4	0	6	1	8	7	0		2024	7	9	8	6	1	2	2	10	4	8	7	9		125
acool

Cell Formulas
Range	Formula
AB2:AB3	AB2	=SUM(($A2:$AA2)*(--(ISNUMBER($A$1:$AA$1))))

I used my own number values, I'm not going to type all the exact information. But, the formula should work.

@awoohaw Do you know how to apply this formula to account for a scenario using a non-continuous range and sum only the first 12 non-zero numbers?

 

[Fluff]

How about

=SUM(TAKE(FILTER(A2:Z2,(A2:Z2<>0)*(COLUMN(A2:Z2)<>14)),,12))

 

[awoohaw]

I think I understand what is wanted now. I think @Fluff answer may be better, but I'm not sure.

Mr Excel Questions 72.xlsm
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T	U	V	W	X	Y	Z	AA	AB
1	2024-01-01	2024-02-01	2024-03-01	2024-04-01	2024-05-01	2024-06-01	2024-07-01	2024-08-01	2024-09-01	2024-10-01	2024-11-01	2024-12-01	Total	Year	2025-01-01	2025-02-01	2025-03-01	2025-04-01	2025-05-01	2025-06-01	2025-07-01	2025-08-01	2025-09-01	2025-10-01	2025-11-01	2025-12-01	Total	Grand Total
2	10	10	3	7	2	7	3	8	5	10	1	7		2024	4	8	4	8	8	2	3	6	1	3	0	7		73
3	3	3	7	6	7	4	0	6	1	8	7	0		2024	7	9	8	6	1	2	2	10	4	8	7	9		68
acool

Cell Formulas
Range	Formula
AB2:AB3	AB2	=SUM(CHOOSECOLS(FILTER($A2:$AA2,(ISNUMBER($A$1:$AA$1))*($A2:$AA2<>0)),1,2,3,4,5,6,7,8,9,10,11,12))

 

[acool]

@awoohaw Do you know how to apply this formula to account for a scenario using a non-continuous range and sum only the first 12 non-zero numbers?

How about

=SUM(TAKE(FILTER(A2:Z2,(A2:Z2<>0)*(COLUMN(A2:Z2)<>14)),,12))

How about

=SUM(TAKE(FILTER(A2:Z2,(A2:Z2<>0)*(COLUMN(A2:Z2)<>14)),,12))

@Fluff Do you know how to apply this formula to exclude Columns M & N. The formula is currently also summing the "Total" in column M which is skewing the calculation. I would like the formula to only apply to columns where there are dates. Thank You!

 

[Fluff]

You said you wanted A2:M2 & O2:Z2, if you dont want col M then use

=SUM(TAKE(FILTER(A2:Z2,(A2:Z2<>0)*(COLUMN(A2:Z2)<>14)*(COLUMN(A2:Z2)<>13)),,12))

 

[acool]

You said you wanted A2:M2 & O2:Z2, if you dont want col M then use

=SUM(TAKE(FILTER(A2:Z2,(A2:Z2<>0)*(COLUMN(A2:Z2)<>14)*(COLUMN(A2:Z2)<>13)),,12))

This did the trick. Thank you both for your help!

 

[Fluff]

Glad we could help & thanks for the feedback.

 

[acool]

This did the trick. Thank you both for your help!

@Fluff Just curious: When trying this formula on another sheet, it is providing me with the incorrect totals despite applying the same logic of the formula. If I am using the formula that you provided above to sumif cells that have utilize other formula to get to a total, could that mess up the calculation?