google-sheetsgoogle-sheets-formulagoogle-forms

SOLVED How do I check if there data and then run the formula, when using Google Form and Google Sheet?

I'm using a Google Form to collect data, and then it get put into a Google Sheet called Form Responses 1.

I then use the below formula to get the results I need from Form Responses 1, then display them into a Google Sheet.

=COUNTIFS('Form Responses 1'!$C:$C,$A29,'Form Responses 1'!$D:$D,B$27,'Form Responses 1'!$B:$B,"=1 & 2")

This work fine and does display the data, however it display a zero, when there is no data in Form Responses 1 for the formula.

How do I change this to check if there is any data in Form Responses 1 for the formula and then if there is display, if not ignore it.

I have tried isblank, but may of got the formula wrong, or not using the right function.

Solution

  • You can check using COUNTA() function with IF().

    =IF(COUNTA('Form Responses 1'!$C:$C)=0,,COUNTIFS('Form Responses 1'!$C:$C,$A29,'Form Responses 1'!$D:$D,B$27,'Form Responses 1'!$B:$B,"=1 & 2"))

    Or check result of full formula then put null in case of zero.

    =LET(x,COUNTIFS('Form Responses 1'!$C:$C,$A29,'Form Responses 1'!$D:$D,B$27,'Form Responses 1'!$B:$B,"=1 & 2"),IF(x=0,,x))