How to Combine 2 Functions in 1 Cell on a Google Spreadsheet and have the results for the 2nd function appear in the notes for that cell

1
votes

Hi I'm trying to figure out a way to combine Multiple Queries into the same cell on a Google Spreadsheet.

Here is the setup:

I can use =COUNTIF('Form Responses 1'!$BA$2:$BA$201, "Boots")

to check sheet1 to see if "Boots" is in multiple cells in Column BA. Then have it return the count in cell A1 on sheet2.

I can use =QUERY('Form Responses 1'!A2:BS, ("Select B, C where(BA='Boots')")) In a different cell on sheet2 and it will fill 2 columns and 3 rows with the results of the data from columns B and C in sheet1 as it relates to the Query results.

But I can't figure out how to combine both

=COUNTIF('Form Responses 1'!$BA$2:$BA$201, "Boots")

&

=QUERY('Form Responses 1'!A2:BS, ("Select B, C where(BA='Boots')"))

Into the same cell where the =COUNTIF shows in the Cell and the =QUERY results appear in the Notes for the Cell.

Lets say columns B and C are Names and Ages of people and column BA is what they want to buy.

The end result would be to show on sheet 2, in a single cell, both how many people want Boots and in the notes for that cell show a list of their names and ages.

1
google-sheets

1 Answers

4
votes

This worked for me, you can obviously switch the text around and stuff but here you go:

="The following "&COUNTIF(Sheet7!$A$2:$A$201, "Boots")&" People would like to purchase boots:"&char(10)&arrayformula(join(char(10),(QUERY(Sheet7!A2:C, "Select B where A='boots'")&", Age "&QUERY(Sheet7!A2:C, "Select C where A='boots'"))))

the end result text looks like this in a single cell:

The following 2 People would like to purchase boots:
dustin, Age 9
roger, Age 10