CODEWITHSUNDEEP
google-sheetsgoogle-sheets-formula
Naografix
Naografix

Reputation: 959

Google sheets: Count all value where condition

I'm trying to count how many "products" appear in my tables with the same value

I made this little example to explain what I want:

enter image description here

Values excepted (only the count):

As you can see, I want to count how many "Apple" has value 1 in my two tables, how many "Apple" has value 2 etc...

With Google Sheets, I know how to count, but not how to get the value in the right cell.

https://docs.google.com/spreadsheets/d/1Dsby2YS_x5W8-QqoTqzckPUPo1rxuB5_s7ZE3GXdEAc/edit?usp=sharing

Upvotes: 1

Views: 548

Answers (2)

player0
player0

Reputation: 1

use:

=ARRAYFORMULA(QUERY({A3:A&" "&B3:B; C3:C&" "&D3:D}, 
 "select Col1,count(Col1)
  where Col1 <> ' '
  group by Col1"))

enter image description here

Upvotes: 2

Marios
Marios

Reputation: 27380

Explanation:

There might be a better or more practical solution but I will keep it simple.

  • My approach would be to get the data of both tables into a single column so it will be feasible to count the pairs.

  • The first step is to concatenate the index and the value columns and then flatten the resulting columns into one.

Solution:

I am going to use two formulas.

In my example, cell F1 has the main formula:

=flatten({ARRAYFORMULA(A2:A7&" "&B2:B7), ARRAYFORMULA(C2:C7&" "&D2:D7)})

And cell G1 has the count formula:

=arrayformula(if(F1:F<>"",countif($F1:$F,F1:F),""))

Result:

result

Upvotes: 1

Related Questions