Collating costs based upon supplier and month

Copper Contributor

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))

@Twifoo 

 

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?

@Twifoo 

 

See attached - many thanks for you assistance.

 

Jamie.

@Jamie Sproston 

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. 

@Twifoo 

 

That is perfect, thank you kind sir for your help, it's very much appreciated !

 

Have a great day.

 

Jamie.

You're very much welcome!