0

Let's say I have 11 different sheets in one Google Sheet workbook. Each of the sheets look like this:

customer reason for losing
PT A uncompetitive price
PT B by project
PT C NG quality
PT D late delivery
PT E term of payment
PT F late delivery

I want to count the number of each reason for losing categories and create a new sheet page to summarize the count of each reason for losing categories.

I expect the output will look like this:

reason for losing count
uncompetitive price 1
by project 1
late delivery 2
NG quality 1
term of payment 1

I'm thinking of using VLOOKUP, SUM, or COUNTIF but still not sure how to create the best formula. Can anyone help me to build the formula?

Thank u in advance.

Harun24hr
  • 30,391
  • 4
  • 21
  • 36
rainy days.
  • 29
  • 5
  • To summarize data from all 11 sheet, you need `VSTACK()` to stack data then use `QUERY()` function to get desired calculation/aggregation. – Harun24hr Apr 10 '23 at 05:32

2 Answers2

1

Use QUERY() function.

=QUERY(B:B,"select B, count(B) 
where B is not null 
group by B 
label count(B) 'Count'",1)

enter image description here

Harun24hr
  • 30,391
  • 4
  • 21
  • 36
0

Adding to @Harun24hr, you can use VSTACK or curly brackets if you want to have a summary sheet of the 11 pages:

=QUERY({Sheet1!B:B;Sheet2!B2:B;Sheet3!B2:B....},"select Col1, count(Col1) 
where Col1 is not null 
group by Col1 
label count(Col1) 'Count'",1)

One extra option instead of using curly brackets if you have a range with all the sheets names is to use REDUCE and INDIRECT to get all ranges dinamically to get new sheets' names. Guessing that range is in D column of your summary sheet:

=QUERY(REDUCE("reason for losing", TOCOL(D:D,1), LAMBDA(a,v,{a;INDIRECT (v&"!B2:B")})),"select Col1, count(Col1) 
where Col1 is not null 
group by Col1 
label count(Col1) 'Count'",1)
Martín
  • 7,849
  • 2
  • 3
  • 13