CODEWITHSUNDEEP
google-sheetssumaveragegoogle-sheets-formulagoogle-sheets-query
Alvin Sanda
Alvin Sanda

Reputation: 53

Summing values and averaging values while removing duplicates in Excel or Google Sheets

I have a google sheet that I use to track the different occasion I stock my items for small retail reselling, since the things I buy can have different price on different occasion (discounts, cashback, etc), I need to average the cost of a unique item while also adding the total stocks I have. removing the duplicates.

A       B       C               D        E        F     G
ITEMS   STOCKS  PRICE PER PCS   SUBTOTAL DISCOUNT TOTAL FINALPRICE/PCS

ITEM1   3       $2              $6       10%      $5.4  $1.8 
ITEM2   2       $3
ITEM4   2       $1.5
ITEM1   2       $1.8            $3.6     10%      $3.24 $1.62

So right now, I can put the following formula to column J to already remove the duplicates and add the stocks of the duplicates.

={unique(A2:A5),ArrayFormula(sumif(A2:A5,unique(A2:A5),B2:B5))}

result as below,

J       K       
ITEMS   STOCKS  

ITEM1   5       
ITEM2   2      
ITEM4   2

But I would also like to average the price per pcs for unique items on column L. Any help is appreciated!

I tried 

={unique(A2:A5),ArrayFormula(sumif(A2:A5,unique(A2:A5),B2:B5)), ArrayFormula(averageif(A2:A5,unique(A2:A5),G2:G5))}

This is the error code

Error Function ARRAY_ROW parameter 3 has mismatched row size. Expected: 24. Actual: 1.

Upvotes: 0

Views: 140

Answers (1)

player0
player0

Reputation: 1

use QUERY instead like:

=QUERY(A2:G, 
 "select A,sum(B),avg(G) 
  where A is not null 
  group by A
  label sum(B)'',avg(G)''", 0)

0

Upvotes: 0

Related Questions