In the enclosed sample data. I need to calculate month-wise sum of quantity for each year in both ranges M4:X13, M17:X26. I have tried to calculate it thru SUMIFs but its not working. My existing SUMIF formula is mentioned in M4 and M17.
Kindly help.
Last edited by leo73pk; 09-21-2019 at 12:05 PM.
Best Regards,
Hamza
It is the greatest of all mistakes to do nothing because you can only do little - do what you can.
Your SUMIFS formula is correct. It is your data that is wrong.
Two problems:
The year in column B is actually a date formatted to look like a year. Change the formula to
=YEAR(A2)
and change formatting for that column to General.
The month names in M3:X3 are text, and the data in column C are dates formatted to look like text. Change the formula in column C to be
=TEXT(A2,"MMM")
and change formatting for that column to General.
(Also, there was no need to use the ABS function here. Not sure what you had in mind there.)
Last edited by 6StringJazzer; 09-21-2019 at 11:46 AM. Reason: added notes on format
I too would use the above approach, but for what it's worth, here's another way
=SUMIFS($E$2:$E$2528,$A$2:$A$2528,">="&DATE($L4,COLUMNS($M:M),1),$A$2:$A$2528,"<"&DATE($L4,COLUMNS($M:M)+1,1),$D$2:$D$2528,$L$2)
Beth.
Thanks. Issue resolved
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks