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

Twifoo's avatar
Twifoo
Silver Contributor
May 24, 2019
Can you please attach your sample file?
    • Twifoo's avatar
      Twifoo
      Silver Contributor
      May 24, 2019

      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. 

      • Jamie Sproston's avatar
        Jamie Sproston
        Copper Contributor
        May 24, 2019

        Twifoo 

         

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

         

        Have a great day.

         

        Jamie.