Sumif with cell reference as criteria

1. Sumif with cell reference as criteria

Hello,

I have a report that tracks number of calls that we receive in 15 minute intervals during an entire week. Originally, all of my summary formulas were simple sums that I updated daily. Now, however, I am trying to automate it as much as possible to be able to update it much faster. (A simplified version of the report is attached.)

I ran into a problem when I go to cell J5. Originally, the formula that I tried was:

=sumif(\$B\$214:\$V\$214,"Mon","Tue","Wed","Thu","Fri",\$B248:\$P291)

Obviously this didn't work, and I'm not even sure if it makes sense... Essentially, what I am trying to find is this:

When B214 through V214 is Mon, I need the total number of Received (B248:B291), same for Tue through Fri. On Sat and Sun, I need Q and T, respectively, 256-287.
The major problem that I found was that in order to automate this report as much as possible, each cell is linked to another, there are other sheets involved on the full report as well. Since the "Days" in B214-V214 are actually formatted dates and not just string days, I'm not sure how to set the criteria to find the DAY as opposed to the DATE. So although B214 is Mon, it is actually March 1, but I need the formula to look for Mon, not March 1.

Any help will be greatly appreciated :-)

Sonya  Register To Reply

2. Re: Sumif with cell reference as criteria

Hello sonyap,

I'm not sure I understand. Will J5 not always sum column B? What reason would there be for J5 to look at another column than B?

By the way, even if you format a date to show as "Mon", the underlying value is still the date, so you would still search for or compare with the date, not the "Mon".  Register To Reply

3. Re: Sumif with cell reference as criteria

I agree with Teylyn. You'd need to work in the Weekday function, such as
=sumproduct((Weekday(\$B\$214:\$V\$214)<>{7,1})*(\$B248:\$P291)). I haven't looked at the file yet, but hopefully this gives you a head start on working in Weekday to solving your issue.  Register To Reply

4. Re: Sumif with cell reference as criteria

Hi teylyn and darkyam -

We need a formula that will calculate the sum for each day of the week because as the month changes, the 1st will not be a Monday.. This report always starts on the First of the month, but we need it to still calculate the sum differently for MON-FRI and SAT-SUN. So in other words, in April, when 4/1 is a Thursday, the simple sum will still work for 4/1 and 4/2, but since 4/1 is a Sat, we will have to manually change it to reflect the SAT-SUN cells.

Darkyam, I played around a bit with the weekday() function, but didn't get very far. I tried this, but it didn't work:

=IF(WEEKDAY(\$B214)=(WEEKDAY(\$J3)),\$B248:\$B291,(IF(WEEKDAY(\$E214)=(WEEKDAY(\$K3)),\$E248:\$E291,IF(WEEKDAY(\$H214)=(WEEKDAY(\$L3)),\$H248:\$H291,IF(WEEKDAY(\$K214)=(WEEKDAY(\$M3)),\$K248:\$K291,IF(WEEKDAY(\$N214)=(WEEKDAY(\$N3)),\$N248:\$N291))))))

Thanks again for all your help!!  Register To Reply

5. Re: Sumif with cell reference as criteria

OK, I'm still a bit confused. If B214=J3, then naturally E214=K3, H214=L3, etc. Also, if J3 is going to be the first of the month, and so is B9, then all of these tests are redundant. How exactly will the layout look for April? Will it be any different from March? Is there a case in which my above assumptions are untrue?

It should be fairly easy to build a sum formula with Index/Match that meets your requirements if you unmerge rows 9, 113, and 214 and then go to Format Cells -> Alignment -> select Center Across Selection under the Horizontal dropdown menu, but first I need to be clear what will be different for April. Thanks.  Register To Reply

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