sumif

[Hoosier03]

I am using excel 2007. I am trying to add a year to date figure. I am only getting a specific week. My formula looks like this.

=SUMIF('[Plan.xlsm]Salaried $'!$Z$3:$BY$3,$B$5,'[Plan.xlsm]Salaried $'!$Z$5:$BY$5)



$B$5 is week 37. I would like to add all the weeks from week 1 to week 37. I tried SUMIFS as well and could not get it to work.


Any thoughts?

Thanks,

Rodney

 

[barry houdini]

Rather than summing just those weeks equal to B5 try summing for those <= to B5, for that try

=SUMIF('[Plan.xlsm]Salaried $'!$Z$3:$BY$3,"<="&$B$5,'[Plan.xlsm]Salaried $'!$Z$5:$BY$5)

 

[Hoosier03]

That only produces the correct answer if it is set to week 1. Thoughts?


Thanks,

Rodney

 

[Hoosier03]

Also, how can I paste my data into here? Just copying and paste bundles the numbers together. It would be easier to solve if you could see my numbers.

 

[rob.barnes01]

Try a SUMPRODUCT formula:

=SUMPRODUCT(--(Z3:BY3<=B5),Z5:BY5)

Hope this helps.

 

[mfexcel]

pls clarify if your week number is a numeric or text like "week xx"?

 

[Hoosier03]

It is in the format of W(week number) Q. Week 37 reads W37 Q

 

[Hoosier03]

I tried sumproduct.

=SUMPRODUCT('[Risk Mgmt Plan.xlsm]Salaried $'!$Z$3:$BY$3<=B5,'[Risk Mgmt Plan.xlsm]Salaried $'!$Z$5:$BY$5)

It produces a return of zero no matter what week I put in.

 

[Hoosier03]

And B5 is set to that format as well.