# sumif

#### Hoosier03

##### Board Regular
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

### Excel Facts

How can you automate Excel?
Press Alt+F11 from Windows Excel to open the Visual Basic for Applications (VBA) editor.
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)

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

Thanks,

Rodney

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.

Try a SUMPRODUCT formula:

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

Hope this helps.

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

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

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.

And B5 is set to that format as well.

Replies
4
Views
93
Replies
3
Views
197
Replies
1
Views
117
Replies
9
Views
213
Replies
3
Views
292

1,203,145
Messages
6,053,746
Members
444,681
Latest member

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back