Totaling Question Using Sum(IF

[jkktx81]

I am familier with using this type of formula to add columns if the criteria matches such as:
{=SUM(IF('2009LP'!$C$2:'2009LP'!$C$6999="JAN",IF('2009LP'!$D$2:'2009LP'!$D$6999="LP483",'2009LP'!$J$2:'2009LP'!$J$6999,0)))}

It works good for me if adding data from another main entry sheet onto a totaling sheet.

My question: If I want to use the sumif to add data from say coulmn C, but it's only every 4th cell, like C4, C8, C12, C16, C20 so on and so on, is there way to put that in the formula? Because data in the other cells I don't want to add...

 

[T. Valko]

I am familier with using this type of formula to add columns if the criteria matches such as:
{=SUM(IF('2009LP'!$C$2:'2009LP'!$C$6999="JAN",IF('2009LP'!$D$2:'2009LP'!$D$6999="LP483",'2009LP'!$J$2:'2009LP'!$J$6999,0)))}

It works good for me if adding data from another main entry sheet onto a totaling sheet.

My question: If I want to use the sumif to add data from say coulmn C, but it's only every 4th cell, like C4, C8, C12, C16, C20 so on and so on, is there way to put that in the formula? Because data in the other cells I don't want to add...

Like this...

Book1
	C
3	39
4	6
5	60
6	57
7	36
8	83
9	99
10	61
11	12
12	48
13	79
14	33
15	41
16	39
17	24
18	22
19	68
20	51
21	57
Sheet1

If you know for certain that you will never insert new rows before the start of the range then use this version:

=SUMPRODUCT(--(MOD(ROW(C4:C20),4)=0),C4:C20)

If you might insert new rows before the start of the range then use this version:

=SUMPRODUCT(--(MOD(ROW(C4:C20)-ROW(C4),4)=0),C4:C20)

 

[jkktx81]

Thanks; I do need to use the sumif I believe, because I only want to add those cells IF data matches criteria in another column. Will the sumproduct work the same way?

 

[Aladin Akyurek]

I am familier with using this type of formula to add columns if the criteria matches such as:
{=SUM(IF('2009LP'!$C$2:'2009LP'!$C$6999="JAN",IF('2009LP'!$D$2:'2009LP'!$D$6999="LP483",'2009LP'!$J$2:'2009LP'!$J$6999,0)))}

It works good for me if adding data from another main entry sheet onto a totaling sheet.

My question: If I want to use the sumif to add data from say coulmn C, but it's only every 4th cell, like C4, C8, C12, C16, C20 so on and so on, is there way to put that in the formula? Because data in the other cells I don't want to add...

Generic formula...

Control+shift+enter, not just enter:

=SUM(IF(MOD(ROW(C2:C2000)-ROW(C2),N)=0,C2:C2000))

where N can take on values like 2, 3, 4, etc. This makes the summing start with the first cell of the range of interest.

 

[T. Valko]

Thanks; I do need to use the sumif I believe, because I only want to add those cells IF data matches criteria in another column. Will the sumproduct work the same way?

Yes

Book1
	B	C
3	A	39
4	V	6
5	C	60
6	C	57
7	V	36
8	XXX	83
9	F	99
10	G	61
11	H	12
12	XXX	48
13	G	79
14	H	33
15	N	41
16	J	39
17	XXX	24
18	H	22
19	H	68
20	X	51
21	V	57
Sheet1

Sum every 4th cell in column C starting from cell C4 when the corresponding cell in column B = XXX

=SUMPRODUCT(--(B4:B20="XXX"),--(MOD(ROW(C4:C20),4)=0),C4:C20)

=SUMPRODUCT(--(B4:B20="XXX"),--(MOD(ROW(C4:C20)-ROW(C4),4)=0),C4:C20)

 

[jkktx81]

Once again for the help: But I'm still having trouble, let me be more specific because I'm probably not explaining well enough or I just doing something wrong. Here is actually what I'm trying to do:

Sheet names: DISP & DRVST

In DRVST: Need formula in D3 that totals from DISP column C4:C72 and thats only every 4th cell such as C4, C8, C12, C16 all the way to C72,

BUT only if DISP column E, in every 4th cell such as E5, E9, E13, E7 ALL THE WAY TO E73 has PGEK in the the cell.

 

[jkktx81]

Let me work with T-Valko's last response. I just seen that. Sorry, i will let you know.....

 

[T. Valko]

Once again for the help: But I'm still having trouble, let me be more specific because I'm probably not explaining well enough or I just doing something wrong. Here is actually what I'm trying to do:

Sheet names: DISP & DRVST

In DRVST: Need formula in D3 that totals from DISP column C4:C72 and thats only every 4th cell such as C4, C8, C12, C16 all the way to C72,

BUT only if DISP column E, in every 4th cell such as E5, E9, E13, E7 ALL THE WAY TO E73 has PGEK in the the cell.

Ok, still the same technique, it's just that your ranges are offset from each other.

=SUMPRODUCT(--(DISP!E5:E73="PGEK"),--(MOD(ROW(DISP!C4:C72),4)=0),DISP!C4:C72)

=SUMPRODUCT(--(DISP!E5:E73="PGEK"),--(MOD(ROW(DISP!C4:C72)-ROW(DISP!C4),4)=0),DISP!C4:C72)

 

[jkktx81]

Tried this and didn't work:
=SUMPRODUCT(--(DISP!E5:!E73="PGEK"),--(MOD(ROW(DISP!C4:C20),4)=0),C4:C20))

 

[T. Valko]

Tried this and didn't work:
=SUMPRODUCT(--(DISP!E5:!E73="PGEK"),--(MOD(ROW(DISP!C4:C20),4)=0),C4:C20))

The ranges are not the same size.

Should be C4:C72 in both places.