Forum Discussion
Collating costs based upon supplier and month
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.
7 Replies
- You may try a formula like this:
=SUMPRODUCT(Import!$N$3:$N$10000*(TEXT(MONTH(Import!$C$3:$C$10000),”mmmm”)=B$7)*(Import!$F$3:$F$10000=$A8))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.
- Can you please attach your sample file?
