I want to sum data between two dates and filter it by Project Type. I tried the SUMIFS functions but it gives me a VALUE error. Please Help. I have attached the file below with all the data and calculation area.
I want to sum data between two dates and filter it by Project Type. I tried the SUMIFS functions but it gives me a VALUE error. Please Help. I have attached the file below with all the data and calculation area.
Last edited by hmattoo98; 10-20-2021 at 05:17 AM.
File appears to be password protected. Cannot open file. Please remove and upload an unprotected file that is not linked to any external drive.
Alan עַם יִשְׂרָאֵל חַי
Change an Ugly Report with Power Query
Database Normalization
Complete Guide to Power Query
Man's Mind Stretched to New Dimensions Never Returns to Its Original Form
in N45
=SUMPRODUCT(($B$22:$B$37=N$44)*($F$2:$BB$2>=$L45)*(($F$2:$BB$2<=$M45)*($F$22:$BB$37)))
copy across and down
If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED.
L M N O P Q R S T U V W X Y Z AA AB AC 44 From ToProject 1 Project 2 Project 3 Project 4 Project 5 Project 6 Project 8 Project 10 Project 11 Project 13 Project 14 Project 15 Project 16 Project 17 Project 18 Project 19 45 31-Jan-23 31-Dec-2376,321,875 51,675,000 46,167,188 43,972,396 24,394,531 28,039,500 34,860,938 46,790,625 13,925,781 0 0 0 0 0 0 0 46 31-Jan-24 31-Dec-240 4,306,250 39,571,875 37,690,625 34,152,344 56,079,000 55,777,500 105,278,906 41,777,344 42,407,813 35,092,969 35,092,969 0 0 0 0 47 31-Jan-25 31-Dec-250 0 0 0 0 0 0 0 0 59,370,938 105,278,906 105,278,906 70,185,938 70,185,938 46,790,625 46,790,625 48 31-Jan-26 31-Dec-260 0 0 0 0 0 0 0 0 0 0 0 81,883,594 81,883,594 105,278,906 105,278,906
L M N 44 From ToProject 1 45 31-Jan-23 31-Dec-23=SUM(OFFSET($A$1,MATCH(N$44,$B:$B,0)-1,MATCH($L45,$2:$2,0)-1,1,MATCH($M45,$2:$2,0)-MATCH($L45,$2:$2,0)+1))
Drag formula right and down as long as needed.
Happy with my answer * Add Reputation.
If You are happy with solution, please use Thread tools and mark thread as SOLVED.
I had some problems with downloading too, but eventualy I managed.
With such input data layout SUMIFS would not be the right tool. Use SUMPRODUCTS instead. In N45:
and copy down and rightFormula:Please Login or Register to view this content.
edit: Wow, 3 propositions in the same minute :-)
Last edited by Kaper; 10-20-2021 at 05:01 AM.
Best Regards,
Kaper
Sorry. Here:
At least 3 of us already managed to download previous attachment. See the answers above
Wow Thanks! Would have never guessed. Where Can I learn these skills like you guys?
For information: the minute a workbook I download starts looking for links and trying to get me to log in to other accounts, I close it down. Anyone sharing a workbook here should ensure that the workbook is completely standalone before posting it.
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.
Thank you!! Where Can I learn these skills?
Here!
If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED.
Also, you may not be aware that you can thank those who have helped you by clicking the small star icon located in the lower left corner of the post in which the help was given. By doing so you can add to the reputation(s) of all those who offered help.
A very different approach than the others using SUMPRODUCT but still works. Explain Please?
There are numerous Excel courses/training (some FREE) on the Internet so I would start there.
I suppose that question is to me as I did not use SUMPRODUCT.
I've get array of values from cells which met conditions (project number & time brackets) and then put this array inside SUM.
Ex. for N45: =SUM({6360156.25,6360156.25,6360156.25,6360156.25,6360156.25,6360156.25,6360156.25,6360156.25,6360156.25,6360156.25,6360156.25,6360156.25})
@KOKOSEK: quite right too!!!
There are currently 1 users browsing this thread. (0 members and 1 guests)
Bookmarks