Home

Collating costs based upon supplier and month

%3CLINGO-SUB%20id%3D%22lingo-sub-638105%22%20slang%3D%22en-US%22%3ECollating%20costs%20based%20upon%20supplier%20and%20month%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-638105%22%20slang%3D%22en-US%22%3E%3CP%3EI%E2%80%99m%20trying%20to%20get%20Excel%20to%20collate%20how%20much%20we%20spend%20by%20supplier%20by%20month.%20I%E2%80%99m%20exporting%20a%20year%E2%80%99s%20worth%20of%20purchased%20lines%20from%20our%20MRP%20system%20%E2%80%93%20so%20it%20will%20have%20the%20same%20supplier%20listed%20many%20times.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThis%20is%20pasted%20to%20a%20tab%20named%20%E2%80%9CImport%E2%80%9D%2C%20and%20the%20three%20important%20columns%20are%3A%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ECells%20C3%20%E2%80%93%20C10000%20contain%20delivery%20date%3C%2FP%3E%3CP%3ECells%20F3%20%E2%80%93%20F10000%20contain%20the%20supplier%20name%3C%2FP%3E%3CP%3ECells%20N3%20%E2%80%93%20N10000%20contain%20the%20cost%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20then%20have%20a%20second%20tab%20with%20all%20our%20supplier%20names%20scrolling%20down%2C%20and%20then%20January%20to%20December%20going%20across%20to%20make%20a%20table.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20want%20to%20be%20able%20to%20Paste%20in%20the%20data%2C%20then%20have%20it%20look%20for%20all%20lines%20from%20e.g.%20%E2%80%9Csupplier%20A%E2%80%9D%2C%20with%20a%20January%20delivery%20date%2C%20then%20give%20a%20total%20value%20in%20the%20relevant%20cell.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%E2%80%99ve%20worked%20out%20the%20formula%20to%20give%20me%20totals%20for%20each%20supplier%20for%20the%20year%2C%20but%20I%20can%E2%80%99t%20then%20work%20out%20how%20to%20formulate%20that%20into%20a%20monthly%20breakdown%20as%20well.%20That%20formula%20is%3A%20%3DSUMIF(Import!%24F%243%3A%24F%2410000%2CA8%2CImport!%24N%243%3A%24N%2410000)%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ECell%20A8%20contains%20the%20customer%20name%20as%20an%20FYI.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EAny%20help%20would%20be%20greatly%20appreciated%2C%20as%20online%20searches%20have%20proved%20fruitless.%3C%2FP%3E%3CP%3EMany%20thanks%20in%20advance%2C%3C%2FP%3E%3CP%3EJamie.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-638105%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-638237%22%20slang%3D%22en-US%22%3ERe%3A%20Collating%20costs%20based%20upon%20supplier%20and%20month%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-638237%22%20slang%3D%22en-US%22%3EYou%20may%20try%20a%20formula%20like%20this%3A%3CBR%20%2F%3E%3DSUMPRODUCT(Import!%24N%243%3A%24N%2410000*(TEXT(MONTH(Import!%24C%243%3A%24C%2410000)%2C%E2%80%9Dmmmm%E2%80%9D)%3DB%247)*(Import!%24F%243%3A%24F%2410000%3D%24A8))%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-643030%22%20slang%3D%22en-US%22%3ERe%3A%20Collating%20costs%20based%20upon%20supplier%20and%20month%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-643030%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F280482%22%20target%3D%22_blank%22%3E%40Twifoo%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EMany%20thanks%20for%20the%20response.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI've%20put%20this%20into%20my%20sheet%20but%20I'm%20getting%20%23NAME%3F%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20changed%20%22mmmm%22%20to%20January%20(I%20hope%20this%20was%20right%2C%20I'm%20still%20a%20bit%20of%20a%20novice%20so%20I%20had%20to%20look%20it%20up%20online!)%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EWhat%20is%20the%20%3DB%247%20referencing%3F%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20wonder%20if%20I%20didn't%20give%20enough%20info%20ref%20the%20table%20-%20A8%20contains%20the%20supplier%20code%2C%20but%20the%20grid%20is%20from%20I8%20(January)%20to%20T8%20(December)%20as%20I%20have%20a%20few%20hidden%20columns%20after%20the%20supplier%20name%20(address%2C%20phone%20no%20etc.)%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EMany%20thanks%2C%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EJamie.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-643089%22%20slang%3D%22en-US%22%3ERe%3A%20Collating%20costs%20based%20upon%20supplier%20and%20month%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-643089%22%20slang%3D%22en-US%22%3ECan%20you%20please%20attach%20your%20sample%20file%3F%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-643155%22%20slang%3D%22en-US%22%3ERe%3A%20Collating%20costs%20based%20upon%20supplier%20and%20month%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-643155%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F280482%22%20target%3D%22_blank%22%3E%40Twifoo%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ESee%20attached%20-%20many%20thanks%20for%20you%20assistance.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EJamie.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-643322%22%20slang%3D%22en-US%22%3ERe%3A%20Collating%20costs%20based%20upon%20supplier%20and%20month%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-643322%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F213025%22%20target%3D%22_blank%22%3E%40Jamie%20Sproston%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3EIn%20the%20attached%20edited%20version%20of%20your%20file%2C%20the%20formula%20in%20I8%2C%20copied%20down%20rows%20and%20across%20columns%2C%20is%3A%26nbsp%3B%3C%2FP%3E%3CP%3E%3DSUMPRODUCT(Import!%24N%243%3A%24N%2410000*%3CBR%20%2F%3E(MONTH(Import!%24C%243%3A%24C%2410000)%3D%3CSTRONG%3EI%243%3C%2FSTRONG%3E)*%3CBR%20%2F%3E(Import!%24F%243%3A%24F%2410000%3D%3CSTRONG%3E%24A8%3C%2FSTRONG%3E))%3C%2FP%3E%3CP%3ENote%20the%20use%20of%20mixed%20references%20in%20the%20foregoing%20formula.%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-643358%22%20slang%3D%22en-US%22%3ERe%3A%20Collating%20costs%20based%20upon%20supplier%20and%20month%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-643358%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F280482%22%20target%3D%22_blank%22%3E%40Twifoo%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThat%20is%20perfect%2C%20thank%20you%20kind%20sir%20for%20your%20help%2C%20it's%20very%20much%20appreciated%20!%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EHave%20a%20great%20day.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EJamie.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-643422%22%20slang%3D%22en-US%22%3ERe%3A%20Collating%20costs%20based%20upon%20supplier%20and%20month%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-643422%22%20slang%3D%22en-US%22%3EYou're%20very%20much%20welcome!%3C%2FLINGO-BODY%3E
Jamie Sproston
Occasional 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!
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