SOLVED

COUNTIF help (frequency and volume)

Copper 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
best response confirmed by SorenTheSwede (Copper Contributor)
Solution

@SorenTheSwede 

Hello, see the result below

Abiola1_0-1586260968966.png

 

@SorenTheSwede 

 

You can also use PivotTable to achieve the same result

 

Abiola1_0-1586261610862.png

 

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

Thanks
Done! Sorry, new at this.
1 best response

Accepted Solutions
best response confirmed by SorenTheSwede (Copper Contributor)
Solution

@SorenTheSwede 

Hello, see the result below

Abiola1_0-1586260968966.png

 

View solution in original post