LinkBack URL
About LinkBacks
Hey hey,
i need to get comperison of weekdays vs weekends for each of the months in a year.
I need a sum of all numbers from column B or C or D that meet the criteria of being in january and being saturday or sunday (in uploaded example).
ex1.xlsx
Hey hey,
i need to get comperison of weekdays vs weekends for each of the months in a year.
I need a sum of all numbers from column B or C or D that meet the criteria of being in january and being saturday or sunday (in uploaded example).
ex1.xlsx
Hi,
See the attached file.
I have used a helper column to identify the weekdays and then SUMIFS to calculate the total.
In I4:
=SUMIFS($C$1:$C$40,$A$1:$A$40,">="&I$3,$A$1:$A$40,"<="&J$3)-J4
In J4:
=SUMIFS($C$1:$C$40,A$1:A$40,">="&I$3,$A$1:$A$40,"<="&J$3,$B$1:$B$40,"Saturday")+SUMIFS($C$1:$C$40,$A$1:$A$40,">="&I$3,$A$1:$A$40,"<="&J$3,$B$1:$B$40,"Sunday")
Thank you for the response, unfortunatly i'm trying to find a solution without adding columns. Source data is not in the same file and i'm trying to avoid making changes to it. My bad for not mentioning it in original post.
Sumifs cannot manipulate dates without helper columns.
You could use an ugly and inefficient array formula to do the job, but expect your computer to grind to a halt if your real data range is much bigger than your example.
Formula:
=SUM(IF(TEXT($A$2:$A$40,"mmmm")=LOOKUP("zzz",$H1:H1),IF(H2="Weekdays",IF(WEEKDAY($A$2:$A$40,2)<6,$B$2:$D$40),IF(WEEKDAY($A$2:$A$40,2)>5,$B$2:$D$40))))
*** Must be confirmed as an array formula by using Shift Ctrl and Enter, not just Enter ***
Thanks for the response, i guess helper column is the best way to go then.
Here a couple of things that you might want to consider. I have a different layout of the calculations with the months going down columns and the totals arranged across. I am not clear on whether or not you want the 3 columns totalled together or separately for each month so I have given you both the total for all three columns for weekdays and weekends for the three columns for each month and the totals for each column for weekdays and weekends for each month. This is set up to handle 10000 rows of data.
Dates for both workbooks are set up in columns G and H
Sample formula without named ranges. This calculates all weekdays for January for all columns:
Formula:=SUMPRODUCT(($A$2:$A$10000>=$G5)*($A$2:$A$10000<=$H5)*($E$2:$E$10000<=5)*($B$2:$D$10000))
In the second workbook I have used the same arrangement but have added dynamic named ranges and the output in a table that will total the named ranges as data covering additional months is added to columns A to D. This is set up for 10,000 rows. As a month is added to the data, just click on the last cell of the table (lower right) and hit the tab key. A new row already populated with the calculations will be added.
This formula is the same calculation as above in the file with named ranges:
Formula:=SUMPRODUCT((Date>=$G5)*(Date<=$H5)*(Day<=5)*(ALL))
<---------If you like someone's answer, click the star to the left of one of their posts to give them a reputation point for that answer.Ron W
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
Register To Reply
Bookmarks