WS attached
I need a sumif formula based on date ranges entered.
Formula goes into J5
Start Date Range: F1 , Stop Date Range: F2
I need a sum from J11:J1110 only for the matching text in C5
based on the date range from E11:E1110 that fall between a date range entered into F1-F2.
Using your posted workbook...
I converted your data to an Excel Table named Table1. There's no need to use a fixed range with many blank rows. The Excel Table will automaticaly expand as new data rows are added. Also, formulas that reference Tables have Structured References which automatically self-document.
This regular formula, copied down through J6 returns the total price for the items and dates in the reference cells
The results are...(see attached file)
J5: =SUMIFS(Table1[Price],Table1[ITEM],$C5,Table1[Date],">="&$F$1,Table1[Date],"<="&$F$2)
Is that something you can work with?Apple: $31.03 Pear: $37.02
no, its not working. I would prefer to have the formula and use in reg excel sheet so i can adjust it as needed. Do you have that?
Well..."no, its not working" doesn't really describe the issue. I had no target values to match.
Attached is a workbook without the table and with adjusted formulas
J5:IMHO...not as elegant as:=SUMIFS(Sheet1!$J$11:$J$1010,Sheet1!$D$11:$D$1010,$C5,Sheet1!$E$11:$E$1010,">="&$F$1,Sheet1!$E$11:$E$1010,"<="&$F$2)
Is that something you can work with?J5: =SUMIFS(Table1[Price],Table1[ITEM],$C5,Table1[Date],">="&$F$1,Table1[Date],"<="&$F$2)
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks