Sumif over multiple ranges

blindbe2

New Member
Joined
Nov 30, 2005
Messages
7
I'm trying to sum over 4 weekly ranges if the corresponding range is greater than zero.

Formula is:
=SUMIF(O4:O10,O14:O20,O24:O28,O32:O37,">0",K4:K10,K14:K20,K24:K28,K32:K37)

The problem seems to be in the O32:037 as it doesn't turn a color like the other ranges when it is entered. Additionally the entire sum range does not turn a color.

is there another way to do it that is permissible? Right now it is returning a #value error in the destination cell.

Thanks
 

Some videos you may like

Excel Facts

Move date out one month or year
Use =EDATE(A2,1) for one month later. Use EDATE(A2,12) for one year later.

barry houdini

MrExcel MVP
Joined
Mar 23, 2005
Messages
20,825
I don't believe you can use multiple ranges with SUMIF, what's in the intermediate cells i.e. O11, K11, O12, K12 etc.?
 

blindbe2

New Member
Joined
Nov 30, 2005
Messages
7
This is a 2 week example. I'd like to total after each week and have a monthly total at the end. The "?" represents where I'm trying to get the #WIN and #PD to total.
Milwaukee Contracts 2006.xls
KLMNOP
3# WIN#PD# OTHERJIM A.BUD
42250
51575
66330
7102,550
8
9
10
11181-3,130575
12
13# WIN#PD# OTHERJIM A.BUD
1463,826
153214,200
1664,120
174419,850
18
19
20
217612-23,97018,026
22
23JIM A.BUD
249413-27,10018,601
25
26#WIN??
27#PD??
Jan
 

blindbe2

New Member
Joined
Nov 30, 2005
Messages
7
I figured it out myself. Sumif function does not support multiple ranges as a single function. My final function:

=SUMIF(O4:O10,">0",K4:K10)+SUMIF(O14:O20,">0",K14:K20)+SUMIF(O24:O28,">0",K24:K28)+SUMIF(O32:O37,">0",K32:K37)

This works well.
 

Watch MrExcel Video

Forum statistics

Threads
1,118,797
Messages
5,574,366
Members
412,589
Latest member
ArtBOM
Top