Hi,
In the attached workbook ("Summary" tab) I am trying to get a sum of all values for a particular department (given in column C) and for a particular month ("Data-Detail" tab, Row # 2). When you open the file, it will be easier to understand what I need by looking at the table to populate.
I have tried everything from sumifs, sumproduct but haven't been able to get anything. Any help will be highly appreciated.
Thanks,
Syed
Try this, copied down and across...
=SUMPRODUCT((TEXT('Data-Detail'!$C$2:$AM$2,"mmmm")=Summary!D$2)*('Data-Detail'!$B$4:$B$60=Summary!$C3)*('Data-Detail'!$C$4:$AM$60))
1. Use code tags for VBA. [code] Your Code [/code] (or use the # button)
2. If your question is resolved, mark it SOLVED using the thread tools
3. Click on the star if you think someone helped you
Regards
Ford
Thank you! It is working. Now all I need to do is to understand this formula for future use. Thanks again!
Its actually pretty simple when you break it down
=SUMPRODUCT((TEXT('Data-Detail'!$C$2:$AM$2,"mmmm")=Summary!D$2)*('Data-Detail'!$B$4:$B$60=Summary!$C3)*('Data-Detail'!$C$4:$AM$60))
The 1st part is converting the dates in 'Data-Detail'!$C$2:$AM$2 to month names, using TEXT('Data-Detail'!$C$2:$AM$2,"mmmm") and then comparing it to Summary!D$2)
This gives us the columns to use
the 2nd part ID's the row to use...
'Data-Detail'!$B$4:$B$60=Summary!$C3
Those 2, together, give us the range we are interested in.
the final part gives the entire range that we are working with...
'Data-Detail'!$C$4:$AM$60
So, it says (for January)....
find the columns in the range 'Data-Detail'!$C$2:$AM$2 that = January and find the row in the range 'Data-Detail'!$B$4:$B$60 that = IDL 530 - Variance $ and then add up all the values in the entire range of 'Data-Detail'!$C$4:$AM$60 that falls in that range of columns and row
Here's another option for D3, copied down and right...
=SUMIFS(INDIRECT("'Data-Detail'!"&ROW()*5-4&":"&ROW()*5-4),'Data-Detail'!$2:$2,">="&Summary!D$2,'Data-Detail'!$2:$2,"<="&EOMONTH(Summary!D$2,0))
...and for D14, copied down and right...
=SUMIFS(INDIRECT("'Data-Detail'!"&ROW()*5+14&":"&ROW()*5+14),'Data-Detail'!$2:$2,">="&Summary!D$2,'Data-Detail'!$2:$2,"<="&EOMONTH(Summary!D$2,0))
Nice, jhrenI thought about doing indirect and/or offset, but was trying to avoid the obvious volatile formulas ( I know, SP is vloatile as well)
Ohh! I forgot to mention that I changed summary headers to an actual date (e.g. 1/1/15 for January), displaying just the month, and filling right, incremented by 1 month. Otherwise my formulas won't work unless changed to resemble the TEXT part of yours...
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks