# SUMIFS with multiple columns for current week

1. ## SUMIFS with multiple columns for current week

Hello everyone. First time posting. Actually first day being a member.

This might have been asked before. Point me in the right direction if so. I have a spreadsheet that we keep track of one of our employees vacation, overtime and attendance. I attached the file. I am trying to track overtime for both current week and last week. The current week overtime is in cell N5 and previous week is in cell N4. The formula in cell N5 works if I only do January. But when I try to include more than one month it gives an error. If the supervisor puts in "OT" in the code field, it will add it to the overtime. Am I over thinking this? Is there a better way?

Thanks.
Vince

2. ## Re: SUMIFS with multiple columns for current week

You can use an array formula - enter this into N5 using Ctrl-Shift-Enter instead of just enter:

=SUM(IF(C27:BG57="ot",IF(B27:BF57>=N6,IF(B27:BF57<N6+7,D27:BH57))))

I would also suggest that you do a global spell check - Absense is actually Absence, and so on.... It never looks good to have typos and spelling errors in a work document.

3. ## Re: SUMIFS with multiple columns for current week

Hi!

You could try:
[N5] =SUMIFS(D27:AV57,C27:AU57,"OT",B27:AT57,">="&N6,B27:AT57,"<"&N6+7)
Blessings!

4. ## Re: SUMIFS with multiple columns for current week

Much better than mine - I did that and it gave me an error - but I must have had an unbalanced range when I thought I didn't - -head smack-

5. ## Re: SUMIFS with multiple columns for current week

Wow....Thanks guys. That was fast. I was hoping I had a hard one. I tried both of them. I guess I never realized you can select the whole area and still get what I need.

Thanks....Much appreciated.

There are currently 1 users browsing this thread. (0 members and 1 guests)

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1