CODEWITHSUNDEEP
google-sheetsmatchgoogle-sheets-formulacountifgoogle-sheets-query
RustyTones
RustyTones

Reputation: 69

Looking for unique values then count values from another column with index and match and countif

I've been struggling with an index and Match formula together with countif to figure out how to look in column B (clacc_no) to look for unique values and then to count the number of times order_no (column A) that relate to the unique value in column B. In the image below you will notice clacc_no 84242 is shown twice but there are two order_no that relate to this, so I would like to receive this as the count.

In addition, I would then separately like to sum up the order_total_sell (column C) related to these unique values in Column B.

Can anyone point me in the right direction as Index and Match with countif and sum are not working for me unless I'm just doing something wrong?

Many thanks for reading.

enter image description here

Upvotes: 1

Views: 493

Answers (1)

player0
player0

Reputation: 1

try:

=QUERY(B2:C, 
 "select B,count(B),sum(C),avg(C) 
  where B is not null 
  group by B 
  label count(B)'No. of Orders',sum(C)'Total Value',avg(C)'Average Value'")

0

Upvotes: 1

Related Questions