Hi Guys,
Not sure whether is it possible but I would like to sum the total price of an work order (Sheet Attendance @ Column C2) within a date range specified.
My formula i tried based on my understand around the web, is written at Sheet Report @ Col. I6)
However it keep return as #value despite formatting the data to dates.
Or is it i cant simply sum a range of dates within a range of columns specified?
Please advise on this. Thank you
Last edited by erikku; 04-12-2017 at 09:15 PM.
Welcome to the forum and thanks for attaching a workbook!
You are going to need to do a bit of explaining ... In your formula:
=SUMIFS(Attendance!AP:AP,Attendance!H:AN,">="&Report!D1,Attendance!H:AN,"<="&F1,Attendance!C:C,Report!C6)
the range H:AN is referenced, but there is no data in it, so what is Excel expected to do with it? Why is it being referenced at all when the calculation needed is using summary data from column AP? I really don't understand how you are expecting to identify data between dates when there is no data in the date columns on the attendance sheet.
Ali
Enthusiastic self-taught user of MS Excel who's always learning!
Don't forget to say "thank you" in your thread to anyone who has offered you help.
You can reward them by clicking on * Add Reputation below their user name on the left, if you wish.
Forum Rules (updated August 2023): please read them here.
Hi AliGW, hi Erikku
Column A in "attendance" sheet could be helpful to match relevant date range
In I6
=SUMIFS(Attendance!AP:AP,Attendance!C:C,C6,Attendance!A:A,">="&MONTH(D$1),Attendance!A:A,"<="&MONTH(F$1))
Just an attempt, I hope it could be of some help.
Last edited by canapone; 04-12-2017 at 06:02 AM.
-----------------------------------------------------
At Excelforum, you can say "Thank you!" by clicking the star icon ("Add Reputation") below the post.
Please, mark your thread [SOLVED] if you received your answer.
Hi Aligw, Thanks for the pointers i will further explain it on this.Originally Posted by AliGW
Welcome to the forum and thanks for attaching a workbook!
You are going to need to do a bit of explaining ... In your formula:
=SUMIFS(Attendance!AP:AP,Attendance!H:AN,">="&Report!D1,Attendance!H:AN,"<="&F1,Attendance!C:C,Report!C6)
the range H:AN is referenced, but there is no data in it, so what is Excel expected to do with it? Why is it being referenced at all when the calculation needed is using summary data from column AP? I really don't understand how you are expecting to identify data between dates when there is no data in the date columns on the attendance sheet.
The reason why i referenced range H:AN is i wanted to referenced the dates (Range: H:AN) as the criteria range data for it to match with the date at "Report!D1".
I wanted the sumif function to be able to pick up total price based at "Attendance!AP:AP" so long if fall within the date range stated between "Report!D1" and "Report!C6".
Yes, thanks a lot for this method does get what i wanted. I guess most probably my formula will not work because the dates are set horizontally across from range H:AN.
Last edited by erikku; 04-12-2017 at 09:14 PM.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks