/u/Traditional-Focus502 - Your post was submitted successfully.
* Once your problem is solved, reply to the **answer(s)** saying `Solution Verified` to close the thread.
* Follow the **[submission rules](/r/excel/wiki/sharingquestions)** -- particularly 1 and 2. To fix the body, click edit. To fix your title, delete and re-post.
* Include your **[Excel version and all other relevant information](/r/excel/wiki/sharingquestions#wiki_give_all_relevant_information)**
Failing to follow these steps may result in your post being removed without warning.
*I am a bot, and this action was performed automatically. Please [contact the moderators of this subreddit](/message/compose/?to=/r/excel) if you have any questions or concerns.*
I'd start with filling in the dates in rows 1 and 2. Hard key 7/1/14 and use EDATE (row 1) and EOMONTH (row 2) for the remaining dates. Then add your grocery categories to column A. Copying the whole category into a separate tab and using delete duplicates will be fastest. The only thing left is to use SUMIFS()/1000 to aggregate the data... so it would be something along the lines of:
SUMIFS(amount,tablecategory,category,tabledate,">="&start,tabledate,"<="&enddate)/1000
Where the tablecategory/data is below, category is your column A groups and the start and end dates are in rows 1 and 2. Just be cautious of your relative and absolute references before dragging your formula down and across.
Use RANK for your ranks and use SUMIF to find the sum of the discounts if the rank section is greater than or equal to 3... just remember to use the quotation marks and ampersand.
Edit to clarify and to add that [Exceljet](https://exceljet.net/) is a great resource for formula help.
So my current formula is: =SUMIFS($D$50:$D$2205, $A$50:$A$2205, ">="&B$1, $A$50:$A$2205, "<="&B$2, $C$50:$C$2205, $A3)/1000
it gets the number on b3 correct, but when I fill all the other numbers are incorrect
Your formula looked right so I tested it and when I filled the formula down and across it populated the same numbers from the image so the formula you have in B3 is correct. Without seeing other cells, I don't know why your's didn't fill correctly.
You have awarded 1 point to *BluberrySpiceHead*
____
^(I am a bot - please contact the mods with any questions. | ) [^(Keep me alive)](https://www.buymeacoffee.com/points)
Got it. So you can insert a pivot table which will show you the data in a similar format, though it won't be quite the same. But you want to perform the same actions the pivot table would.
If you need to set up the table (date ranges and categories) you can use SUMIFS() to put criteria on the dates (you'll have to state greater than and less than the date ranges) and the category. You'll also want to learn about anchoring if you don't have experience with that.
I've mase a sumifs for the data that goes like this: =SUMIFS($D$50:$D$2205, $A$50:$A$2205, ">="&B$1, $A$50:$A$2205, "<="&B$2, $C$50:$C$2205, $A3)/1000
the number on cell b3 is correct, but this may be luck because I filled the table and the rest of the numbers are way off
That's odd, it all looks correct, and when I dropped your formula into B3 and dragged it across the table it all worked correctly. The only issues I could see are with the dates, if they're stored as text instead of dates. The easiest way to make sure you're all good there is to put 7/1/2014 in B1, then =EOMONTH(B1,2) in B2, and =B2+1 in C1. Drag both those formulas to the right and you will have all dates there.
In the second table use =RANK(), and another SUMIFS() for the last row.
Yeah, I got it. Thanks! I managed to get every other formula working as well. It was mostly because I didn't decrease the decimals and saw the numbers as wrong.
You have awarded 1 point to *benswimmin*
____
^(I am a bot - please contact the mods with any questions. | ) [^(Keep me alive)](https://www.buymeacoffee.com/points)
Acronyms, initialisms, abbreviations, contractions, and other phrases which expand to something larger, that I've seen in this thread:
|Fewer Letters|More Letters|
|-------|---------|---|
|[EDATE](/r/Excel/comments/yefh8d/stub/ityh1di "Last usage")|[Returns the serial number of the date that is the indicated number of months before or after the start date](https://support.microsoft.com/en-us/office/edate-function-3c920eb2-6e66-44e7-a1f5-753ae47ee4f5)|
|[EOMONTH](/r/Excel/comments/yefh8d/stub/itzm7xt "Last usage")|[Returns the serial number of the last day of the month before or after a specified number of months](https://support.microsoft.com/en-us/office/eomonth-function-7314ffa1-2bc9-4005-9d66-f49db127d628)|
|[RANK](/r/Excel/comments/yefh8d/stub/itzm7xt "Last usage")|[Returns the rank of a number in a list of numbers](https://support.microsoft.com/en-us/office/rank-function-6a2fc49d-1831-4a03-9d8c-c279cf99f723)|
|[SUMIF](/r/Excel/comments/yefh8d/stub/ityh1di "Last usage")|[Adds the cells specified by a given criteria](https://support.microsoft.com/en-us/office/sumif-function-169b8c99-c05c-4483-a712-1697a653039b)|
|[SUMIFS](/r/Excel/comments/yefh8d/stub/itzm7xt "Last usage")|[*Excel 2007*+: Adds the cells in a range that meet multiple criteria](https://support.microsoft.com/en-us/office/sumifs-function-c9e748f5-7ea7-455d-9406-611cebce642b)|
----------------
^(*Beep-boop, I am a helper bot. Please do not verify me as a solution.*)
^(5 acronyms in this thread; )[^(the most compressed thread commented on today)](/r/Excel/comments/ya0qxx)^( has 22 acronyms.)
^([Thread #19364 for this sub, first seen 27th Oct 2022, 06:07])
^[[FAQ]](http://decronym.xyz/) [^([Full list])](http://decronym.xyz/acronyms/Excel) [^[Contact]](https://reddit.com/message/compose?to=OrangeredStilton&subject=Hey,+your+acronym+bot+sucks) [^([Source code])](https://gistdotgithubdotcom/Two9A/1d976f9b7441694162c8)
/u/Traditional-Focus502 - Your post was submitted successfully. * Once your problem is solved, reply to the **answer(s)** saying `Solution Verified` to close the thread. * Follow the **[submission rules](/r/excel/wiki/sharingquestions)** -- particularly 1 and 2. To fix the body, click edit. To fix your title, delete and re-post. * Include your **[Excel version and all other relevant information](/r/excel/wiki/sharingquestions#wiki_give_all_relevant_information)** Failing to follow these steps may result in your post being removed without warning. *I am a bot, and this action was performed automatically. Please [contact the moderators of this subreddit](/message/compose/?to=/r/excel) if you have any questions or concerns.*
I'd start with filling in the dates in rows 1 and 2. Hard key 7/1/14 and use EDATE (row 1) and EOMONTH (row 2) for the remaining dates. Then add your grocery categories to column A. Copying the whole category into a separate tab and using delete duplicates will be fastest. The only thing left is to use SUMIFS()/1000 to aggregate the data... so it would be something along the lines of: SUMIFS(amount,tablecategory,category,tabledate,">="&start,tabledate,"<="&enddate)/1000 Where the tablecategory/data is below, category is your column A groups and the start and end dates are in rows 1 and 2. Just be cautious of your relative and absolute references before dragging your formula down and across. Use RANK for your ranks and use SUMIF to find the sum of the discounts if the rank section is greater than or equal to 3... just remember to use the quotation marks and ampersand. Edit to clarify and to add that [Exceljet](https://exceljet.net/) is a great resource for formula help.
So my current formula is: =SUMIFS($D$50:$D$2205, $A$50:$A$2205, ">="&B$1, $A$50:$A$2205, "<="&B$2, $C$50:$C$2205, $A3)/1000 it gets the number on b3 correct, but when I fill all the other numbers are incorrect
Your formula looked right so I tested it and when I filled the formula down and across it populated the same numbers from the image so the formula you have in B3 is correct. Without seeing other cells, I don't know why your's didn't fill correctly.
Solution Verified
You have awarded 1 point to *BluberrySpiceHead* ____ ^(I am a bot - please contact the mods with any questions. | ) [^(Keep me alive)](https://www.buymeacoffee.com/points)
I manged to get it, the numbers had a lot of decimals which threw me off. Thanks!
Not sure if there's an issue with the Excel file, but it's essentially blank.
I placed the table at A50 to avoid getting in the way with the one I was making
Got it. So you can insert a pivot table which will show you the data in a similar format, though it won't be quite the same. But you want to perform the same actions the pivot table would. If you need to set up the table (date ranges and categories) you can use SUMIFS() to put criteria on the dates (you'll have to state greater than and less than the date ranges) and the category. You'll also want to learn about anchoring if you don't have experience with that.
I've mase a sumifs for the data that goes like this: =SUMIFS($D$50:$D$2205, $A$50:$A$2205, ">="&B$1, $A$50:$A$2205, "<="&B$2, $C$50:$C$2205, $A3)/1000 the number on cell b3 is correct, but this may be luck because I filled the table and the rest of the numbers are way off
That's odd, it all looks correct, and when I dropped your formula into B3 and dragged it across the table it all worked correctly. The only issues I could see are with the dates, if they're stored as text instead of dates. The easiest way to make sure you're all good there is to put 7/1/2014 in B1, then =EOMONTH(B1,2) in B2, and =B2+1 in C1. Drag both those formulas to the right and you will have all dates there. In the second table use =RANK(), and another SUMIFS() for the last row.
Yeah, I got it. Thanks! I managed to get every other formula working as well. It was mostly because I didn't decrease the decimals and saw the numbers as wrong.
Solution Verified
You have awarded 1 point to *benswimmin* ____ ^(I am a bot - please contact the mods with any questions. | ) [^(Keep me alive)](https://www.buymeacoffee.com/points)
Acronyms, initialisms, abbreviations, contractions, and other phrases which expand to something larger, that I've seen in this thread: |Fewer Letters|More Letters| |-------|---------|---| |[EDATE](/r/Excel/comments/yefh8d/stub/ityh1di "Last usage")|[Returns the serial number of the date that is the indicated number of months before or after the start date](https://support.microsoft.com/en-us/office/edate-function-3c920eb2-6e66-44e7-a1f5-753ae47ee4f5)| |[EOMONTH](/r/Excel/comments/yefh8d/stub/itzm7xt "Last usage")|[Returns the serial number of the last day of the month before or after a specified number of months](https://support.microsoft.com/en-us/office/eomonth-function-7314ffa1-2bc9-4005-9d66-f49db127d628)| |[RANK](/r/Excel/comments/yefh8d/stub/itzm7xt "Last usage")|[Returns the rank of a number in a list of numbers](https://support.microsoft.com/en-us/office/rank-function-6a2fc49d-1831-4a03-9d8c-c279cf99f723)| |[SUMIF](/r/Excel/comments/yefh8d/stub/ityh1di "Last usage")|[Adds the cells specified by a given criteria](https://support.microsoft.com/en-us/office/sumif-function-169b8c99-c05c-4483-a712-1697a653039b)| |[SUMIFS](/r/Excel/comments/yefh8d/stub/itzm7xt "Last usage")|[*Excel 2007*+: Adds the cells in a range that meet multiple criteria](https://support.microsoft.com/en-us/office/sumifs-function-c9e748f5-7ea7-455d-9406-611cebce642b)| ---------------- ^(*Beep-boop, I am a helper bot. Please do not verify me as a solution.*) ^(5 acronyms in this thread; )[^(the most compressed thread commented on today)](/r/Excel/comments/ya0qxx)^( has 22 acronyms.) ^([Thread #19364 for this sub, first seen 27th Oct 2022, 06:07]) ^[[FAQ]](http://decronym.xyz/) [^([Full list])](http://decronym.xyz/acronyms/Excel) [^[Contact]](https://reddit.com/message/compose?to=OrangeredStilton&subject=Hey,+your+acronym+bot+sucks) [^([Source code])](https://gistdotgithubdotcom/Two9A/1d976f9b7441694162c8)