May 23 2019 08:00 AM
I’m trying to get Excel to collate how much we spend by supplier by month. I’m exporting a year’s worth of purchased lines from our MRP system – so it will have the same supplier listed many times.
This is pasted to a tab named “Import”, and the three important columns are:
Cells C3 – C10000 contain delivery date
Cells F3 – F10000 contain the supplier name
Cells N3 – N10000 contain the cost
I then have a second tab with all our supplier names scrolling down, and then January to December going across to make a table.
I want to be able to Paste in the data, then have it look for all lines from e.g. “supplier A”, with a January delivery date, then give a total value in the relevant cell.
I’ve worked out the formula to give me totals for each supplier for the year, but I can’t then work out how to formulate that into a monthly breakdown as well. That formula is: =SUMIF(Import!$F$3:$F$10000,A8,Import!$N$3:$N$10000)
Cell A8 contains the customer name as an FYI.
Any help would be greatly appreciated, as online searches have proved fruitless.
Many thanks in advance,
Jamie.
May 23 2019 08:49 AM
May 24 2019 12:16 AM
Many thanks for the response.
I've put this into my sheet but I'm getting #NAME?
I changed "mmmm" to January (I hope this was right, I'm still a bit of a novice so I had to look it up online!)
What is the =B$7 referencing?
I wonder if I didn't give enough info ref the table - A8 contains the supplier code, but the grid is from I8 (January) to T8 (December) as I have a few hidden columns after the supplier name (address, phone no etc.)
Many thanks,
Jamie.
May 24 2019 12:36 AM
May 24 2019 01:10 AM
May 24 2019 02:28 AM
In the attached edited version of your file, the formula in I8, copied down rows and across columns, is:
=SUMPRODUCT(Import!$N$3:$N$10000*
(MONTH(Import!$C$3:$C$10000)=I$3)*
(Import!$F$3:$F$10000=$A8))
Note the use of mixed references in the foregoing formula.
May 24 2019 02:47 AM
That is perfect, thank you kind sir for your help, it's very much appreciated !
Have a great day.
Jamie.