conditional sum across columns

[JV0710]

Hi

Please can I get some help with a formula for this query:

On my Sheet I have in Row3 ( from A3 ), headings for months and weeks - so in A3 I have "January" and in B3 I have - week1, in c3 - week2, . . . in f3 - February, g3 -week1 etc

I row 4, I have values in the week columns adding up the month columns.

In A1 I have a number = 8

see example:

Book1
	A	B	C	D	E	F	G	H	I	J	K	L	M
1	8
2
3	January	w	w	w	w	February	w	w	w	w	March	w	w
4	40	10	10	10	10	40	10	10	10	10	50	10	10
Sheet1

I B6 I want to sum the next 8 ( value in A1 ) weeks data from C6

something like Sumproduct(--(left(C3:dd3)="w")*(c4:dd4)) but only for the next 8 instances of "weeks"

If the value in A1 changes to 5 the it must sum the next 5 weeks data etc

I hope that makes sense

Thank you

JVN

 

[number kruncher]

I am now eating humble pie

 

[JV0710]

Hi Guys

=SUMIF(B3:OFFSET(B3,0,A1-1),"w",B4:OFFSET(B4,0,A1-1))

This does not work if the range stretches over 2 months

And the summing has to begin from column c if I am in column B

Thanks

JVN

 

[T. Valko]

Hi Guys

=SUMIF(B3:OFFSET(B3,0,A1-1),"w",B4:OFFSET(B4,0,A1-1))

This does not work if the range stretches over 2 months

And the summing has to begin from column c if I am in column B

Thanks

JVN

Try the formula I suggested.

Not sure what you mean by: "if I am in column B".

You just have to adjust the ranges to fit your needs.

 

[JV0710]

Thank you T Valko

Your Answer works fine

What I meant by "If I am in column B" ??
I will copy the formula across to c6,d6, etc

Thank you very much for your help

StuLux: I will also add in the enhancements to make sure that at month level there is no value etc

Thank you once again

 

[T. Valko]

I am now eating humble pie

Been there, done that!

 

[T. Valko]

Thank you T Valko

Your Answer works fine

What I meant by "If I am in column B" ??
I will copy the formula across to c6,d6, etc

Thank you very much for your help

StuLux: I will also add in the enhancements to make sure that at month level there is no value etc

Thank you once again

Good deal. Thanks for the feedback!