Forum Discussion

Jamie Sproston's avatar
Jamie Sproston
Copper Contributor
May 23, 2019

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. ...
excel
Twifoo's avatar
Twifoo
Silver Contributor
May 23, 2019
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))
  • Jamie Sproston's avatar
    Jamie Sproston
    Copper Contributor
    May 24, 2019

    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.