sum column by two criteria

[coolhit]

Hi

I would to sum a column but by two criteria. My data is layed out as below

Column=C Column=F Column=G Column=H
Multi Part Wk=1 Wk=2 Wk=3
M 5 5 1
M 6 10 5
M 0 5 0
0 0 0 0
M 10 10 6
0 0 0 0
M 5 10 0

I want to sum all "m" by week no(as i have 52 columns, 1 for each week) in following layout.

wk1 wk2 wk3
M 26 40 12

Please can someone help.

[Spencer101]

Can I just clarify that you want to sum by week, so a total of all M's in week 1, then all M's in week 2 and so on?

[Richard Buttrey]

Hi,

=SUMIF($C:$C,"M",F:F)

will sum all week 1. Copy across for other weeks.

Regards

[coolhit]

Hi,

=SUMIF($C:$C,"M",F:F)

will sum all week 1. Copy across for other weeks.

Regards

Hi Richard,

Thank you for the reply.

I just thought of some thing, don't know if you could help me tweak the formula

My current layout is as below

wk1 wk2 wk3
M 26 40 12

I thought that can it sum all "m" but check which week no is at the top and total that column (ie. i have replace wk 1 with wk2, wk 2 with wk 3 and new one is wk4)

wk2 wk3 wk4
M 40 12

If you can help me tweak.

Regards

[Richard Buttrey]

Hi,

Unless I'm missing something obvious you don't need to know what's at the top of the column.

If say column F is wk1, column G, wk2 etc. with the data starting on row 2, just enter the formula I gave you in say BG2 and copy across for 52 columns.

If you want to see the totals in rows down a column instead of in columns across a row then use

=SUMIF(C:C,"M",OFFSET(C:C,0,ROW()-1)) on a row 2 cell and copy down.

But it strikes me that a Pivot Table would be a more elegant solution.

If you're still struggling then upload your workbook and explain with an example what you expect to see.