Sumifs refernce cells/range contains formula

jackfox68

Board Regular
Joined
Jan 24, 2010
Messages
121
Good day,

My SUMIFS statement is referring to a cell/range with a "=text" formula that converts a date into a day of the week, i.e. =TEXT(A1,"ddd"). Essentially what I am trying to do is sum all of the days in January that are not Saturday, and then divide them by the number of days that have past that are also not Saturday. Then I need to multiply the result by the number of the days in the month of January that are not Saturday. I am essentially trying to trend out the monthly call volume, however I need to calculate Saturday's differently because the hours of operations is only a 1/3 of what Mon-Fri is.

Column A is the Month/Year
Column B is the Day
Column C is the Day#
Column D is the Short Date

Thanks

Brian
 

Excel Facts

Using Function Arguments with nested formulas
If writing INDEX in Func. Arguments, type MATCH(. Use the mouse to click inside MATCH in the formula bar. Dialog switches to MATCH.
This will sum all the values in column C where the corresponding date in column A is not a Saturday:

=SUMPRODUCT(--(WEEKDAY($A$2:$A$100,2)<>6),($C$2:$C$100))

Dom
 
Upvote 0
Thanks DOM. I am thrown off by the dashes (--(WEEKDAY(... in the formula you provided. Are those required, or do they represent unknown parts of a formula this would be placed into?
 
Upvote 0
OK - looks like Dashes are required, or at the very last the formula works with the dashes included. Is there a way to include a condition to only sum values that happened in a particular month? Could I build a sumif inside the of the sumproduct?

Maybe use an offset with index/match?
 
Last edited:
Upvote 0
Hi,

Same approach as Dom did.
Try this for dates which have already passed assuming your dates are in Column A:

Code:
=SUMPRODUCT((--(WEEKDAY(OFFSET($A$1,0,0,MATCH(TODAY(),$A$1:$A$31,0),1))<>6)*(--WEEKDAY(OFFSET($A$1,0,0,MATCH(TODAY(),$A$1:$A$31,0),1))<>7))*OFFSET(C1,0,0,MATCH(TODAY(),$A$1:$A$31,0),1))
 
Upvote 0
Hi Brian

You can add a condition for the month in Dom's formula. For ex. for Feb (month 2)

=SUMPRODUCT(--(WEEKDAY($A$2:$A$100,2)<>6),--(MONTH($A$2:$A$100)=2),($C$2:$C$100))
 
Upvote 0
I love this place! Thanks everyone! Can't wait until I am at a level where I can be supplying the answers.
 
Upvote 0

Forum statistics

Threads
1,213,568
Messages
6,114,348
Members
448,570
Latest member
rik81h

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.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

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

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

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
Back
Top