• 408K Members
• 8,787 Online
• 464K Conversations

## Collating costs based upon supplier and month

Occasional Contributor

# 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.

Jamie.

7 Replies

# Re: Collating costs based upon supplier and month

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

# Re: Collating costs based upon supplier and month

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.

# Re: Collating costs based upon supplier and month

See attached - many thanks for you assistance.

Jamie.

# Re: Collating costs based upon supplier and month

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.

# Re: Collating costs based upon supplier and month

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

Have a great day.

Jamie.

# Re: Collating costs based upon supplier and month

You're very much welcome!
Related Conversations
Tabs and Dark Mode
cjc2112 in Discussions on
16 Replies
flashing a white screen while open new tab
cntvertex in Discussions on
11 Replies
How to Prevent Teams from Auto-Launch
chenrylee in Microsoft Teams on
28 Replies
Early preview of Microsoft Edge group policies
Sean Lyndersay in Discussions on
65 Replies