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]

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

If the file isn't too big you could upload it to a free file hosting site and then post a link to the file so I can have a look at it.

Thr other poster won't do that.

 

[T. Valko]

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.

That won't work because the ranges are offset by one row.

You'll get an incorrect result if the first and last cells in the column E range, E5 and E73, meet the condition.

 

[jkktx81]

C3 has validation (List)
C4
=VLOOKUP(C$3,DATA!$D:$F,3,0)
D4
=SUM(C4/2)
E4
=SUM(D4/53)

C & D5 merged.

Is the merges cells the problem you think?

 

[T. Valko]

C3 has validation (List)
C4
=VLOOKUP(C$3,DATA!$D:$F,3,0)
D4
=SUM(C4/2)
E4
=SUM(D4/53)

C & D5 merged.

Is the merges cells the problem you think?

I've just sent you a private message.

 

[Aladin Akyurek]

C3 has validation (List)
C4
=VLOOKUP(C$3,DATA!$D:$F,3,0)
D4
=SUM(C4/2)
E4
=SUM(D4/53)

C & D5 merged.

Is the merges cells the problem you think?

C4:

VLOOKUP can return #N/A.

Let's modify this to:

=IFERROR(VLOOKUP(C$3,DATA!$D:$F,3,0),0)

assuming that you have Excel 2007 or later.

D4 can be reduced to:

=C4/2

E4 can be reduced to:

=D4/53

Let's also unmerge the merged cells.

Now back to to the formula:

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

This formula reads as follow:

Sum every 4th cell, starting with C4, in C4:C72 whenever a correponding cell in E4:E72 (also every 4th cell) is equal to PGEK.

Does this formula, provided that it translates your intention exactly, return the desired total after the modifications above?

 

[T. Valko]

Ok, the OP sent me their file and I found the problem.

There are formulas in both referenced columns that return errors but these errors are hidden using conditional formatting.

Without correcting those formulas the sum formula has morphed into this array formula**:

=SUM(IF(ISTEXT(DISP!E$5:E$73),IF(DISP!E$5:E$73=C3,IF(MOD(ROW(DISP!C$4:C$72)-ROW(DISP!C$4),4)=0,IF(ISNUMBER(DISP!C$4:C$72),DISP!C$4:C$72)))))

** array formulas need to be entered using the key
combination of CTRL,SHIFT,ENTER (not just ENTER).
Hold down both the CTRL key and the SHIFT key
then hit ENTER.

However, I would recommend fixing the formulas causing the errors and then using the originally suggested formula:

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)