SOLVED

COUNTIF help (frequency and volume)

%3CLINGO-SUB%20id%3D%22lingo-sub-1288086%22%20slang%3D%22en-US%22%3ECOUNTIF%20help%20(frequency%20and%20volume)%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1288086%22%20slang%3D%22en-US%22%3E%3CP%3EHi%20all%2C%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI've%20run%20into%20a%20problem%20that%20I'm%20certain%20is%20very%20simple%20to%20solve%20but%20it's%20just%20beyond%20my%20grasp.%20The%20situation%20is%20this%3A%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20have%20created%20an%20excel%20file%20where%20we%20note%20occurrences.%20The%20data%20collected%20(per%20row)%20is%3A%3C%2FP%3E%3CUL%3E%3CLI%3EDate%3C%2FLI%3E%3CLI%3ESKU%3C%2FLI%3E%3CLI%3ELot%20number%3C%2FLI%3E%3CLI%3EQuantity%3C%2FLI%3E%3CLI%3EReason%20for%20occurrence%20(this%20is%20a%20free%20text%20field%20and%20is%20not%20used%20as%20a%20sorting%20criteria%20at%20this%20time)%3C%2FLI%3E%3CLI%3EReporting%20entity%20(see%20above)%3C%2FLI%3E%3CLI%3ELocation%20(ISO%20country%20code)%3C%2FLI%3E%3C%2FUL%3E%3CP%3ENow%2C%20the%20problem%20I've%20run%20into%20is%20that%20I%20want%20to%20summarize%20the%20total%20quantity%20reported%20for%20each%20SKU%20(I%20will%20also%20do%20the%20same%20per%20lot%20number)%20and%20I%20just%20haven't%20been%20able%20to%20figure%20out%20how.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20have%20attached%20an%20example%20of%20the%20basic%20layout%20of%20the%20file%20but%20using%20fewer%20data%20columns.%20In%20column%20A%20we%20have%20the%20report%20date.%20In%20column%20B%20we%20have%20the%20reported%20SKU.%20In%20column%20C%20we%20have%20the%20lot%20number%20of%20the%20reported%20SKU%20and%20in%20column%20D%20we%20have%20the%20quantity%20(multiples)%20reported%20of%20the%20SKU%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EUsing%20the%20formula%26nbsp%3B%3CSTRONG%3E%3DCOUNTIF(%24B%242%3A%24B%2415%3BF2)%3C%2FSTRONG%3E%20in%20column%20GI%20can%20easily%20extrapolate%20the%20number%20of%20times%20(frequency)%20each%20specific%20SKU%20has%20been%20reported%20but%20what%20I%20also%20want%20to%20know%20is%20the%20total%20number%20(volume)%20of%20each%20SKU%20that%20has%20been%20reported%20(column%20H).%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThank%20you%20for%20any%20guidance%20you%20may%20provide.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EP.S.%20As%20I'm%20using%20the%20Swedish%20version%20of%20Excel%20COUNTIF%20is%20translated%20as%20ANTAL.OM.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-1288086%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EFormulas%20and%20Functions%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1288112%22%20slang%3D%22en-US%22%3ERe%3A%20COUNTIF%20help%20(frequency%20and%20volume)%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1288112%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F612376%22%20target%3D%22_blank%22%3E%40SorenTheSwede%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EHello%2C%20see%20the%20result%20below%3C%2FP%3E%0A%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22Abiola1_0-1586260968966.png%22%20style%3D%22width%3A%20400px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Fgxcuf89792.i.lithium.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F182725i97B49D32C45C1F67%2Fimage-size%2Fmedium%3Fv%3D1.0%26amp%3Bpx%3D400%22%20title%3D%22Abiola1_0-1586260968966.png%22%20alt%3D%22Abiola1_0-1586260968966.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1288155%22%20slang%3D%22en-US%22%3ERe%3A%20COUNTIF%20help%20(frequency%20and%20volume)%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1288155%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F612376%22%20target%3D%22_blank%22%3E%40SorenTheSwede%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EYou%20can%20also%20use%20PivotTable%20to%20achieve%20the%20same%20result%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22Abiola1_0-1586261610862.png%22%20style%3D%22width%3A%20400px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Fgxcuf89792.i.lithium.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F182729i12246EE1302C48B4%2Fimage-size%2Fmedium%3Fv%3D1.0%26amp%3Bpx%3D400%22%20title%3D%22Abiola1_0-1586261610862.png%22%20alt%3D%22Abiola1_0-1586261610862.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1288183%22%20slang%3D%22en-US%22%3ERe%3A%20COUNTIF%20help%20(frequency%20and%20volume)%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1288183%22%20slang%3D%22en-US%22%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F421698%22%20target%3D%22_blank%22%3E%40Abiola1%3C%2FA%3E%2C%20thank%20you%20kindly!%3CBR%20%2F%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1288197%22%20slang%3D%22en-US%22%3ERe%3A%20COUNTIF%20help%20(frequency%20and%20volume)%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1288197%22%20slang%3D%22en-US%22%3EYou're%20welcome...%20Would%20appreciate%20you%20accept%20as%20Best%20Response%20if%20I%20correctly%20answer%20your%20question.%3CBR%20%2F%3E%3CBR%20%2F%3EThanks%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1288269%22%20slang%3D%22en-US%22%3ERe%3A%20COUNTIF%20help%20(frequency%20and%20volume)%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1288269%22%20slang%3D%22en-US%22%3EDone!%20Sorry%2C%20new%20at%20this.%3C%2FLINGO-BODY%3E
Highlighted
New Contributor

Hi all,

 

I've run into a problem that I'm certain is very simple to solve but it's just beyond my grasp. The situation is this:

 

I have created an excel file where we note occurrences. The data collected (per row) is:

  • Date
  • SKU
  • Lot number
  • Quantity
  • Reason for occurrence (this is a free text field and is not used as a sorting criteria at this time)
  • Reporting entity (see above)
  • Location (ISO country code)

Now, the problem I've run into is that I want to summarize the total quantity reported for each SKU (I will also do the same per lot number) and I just haven't been able to figure out how.

 

I have attached an example of the basic layout of the file but using fewer data columns. In column A we have the report date. In column B we have the reported SKU. In column C we have the lot number of the reported SKU and in column D we have the quantity (multiples) reported of the SKU

 

Using the formula =COUNTIF($B$2:$B$15;F2) in column GI can easily extrapolate the number of times (frequency) each specific SKU has been reported but what I also want to know is the total number (volume) of each SKU that has been reported (column H).

 

Thank you for any guidance you may provide.

 

P.S. As I'm using the Swedish version of Excel COUNTIF is translated as ANTAL.OM.

5 Replies
Highlighted
Best Response confirmed by SorenTheSwede (New Contributor)
Solution

@SorenTheSwede 

Hello, see the result below

Abiola1_0-1586260968966.png

 

Highlighted

@SorenTheSwede 

 

You can also use PivotTable to achieve the same result

 

Abiola1_0-1586261610862.png

 

Highlighted
@Abiola1, thank you kindly!
Highlighted
You're welcome... Would appreciate you accept as Best Response if I correctly answer your question.

Thanks
Highlighted
Done! Sorry, new at this.