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

 

[jkktx81]

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

Or this didn't.

 

[T. Valko]

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

Or this didn't.

Here's a small sample file that demonstrates this.

ZZZjkktx81.xls 14kb

http://cjoint.com/?AFqs3rYVQhK

I used smaller ranges to make it easier to see what's getting summed without having to scroll up and down the sheet.

 

[jkktx81]

=SUMPRODUCT(--(DISP!E5:E25=C3),--(MOD(ROW(DISP!C4:C24)-ROW(DISP!C4),4)=0),DISP!C4:C24)

IDK what's wrong. I still get #N/A

I have PGEK in C3 just like you do...

 

[Aladin Akyurek]

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.

On DRVST in D3 enter:

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

This is equivalent to a CSE form like:

=SUM(IF(MOD(ROW(DISP!C4:C72)-ROW(DISP!C4),4)=0,IF(DISP!E4:E72="PGEK",DISP!C4:C72)))

The important thing is that this set up (the way the MOD bit is constructed) is robust against row insertions in front of the current data.

 

[jkktx81]

Maybe because DISP sheet has some formulas already in for the data?
It' not standard, has some merge cells and modifications

 

[jkktx81]

Is there another formula not using MOD that I can try?

 

[Aladin Akyurek]

Maybe because DISP sheet has some formulas already in for the data?
It' not standard, has some merge cells and modifications

Having formulas in the range of interest should not be a problem, but merged cells can be troublesome. What do we get - an inaccurate result, an error value?

 

[jkktx81]

Can I send you the worksheet? I took out confidential info and left just the part I'm working on...

 

[jkktx81]

one gives #N/A the other error value

 

[Aladin Akyurek]

Is there another formula not using MOD that I can try?

one gives #N/A the other error value

Care to post the formulas you have on DISP?