Google Sheets - IF ISBLANK QUERY

0
votes

I'm trying to come up with a formula to check whether a column where B='name' is empty or not. I'm using this formula if a cell is blank, add 50 points, if not, add 0 points.

I have the basic formula of =IF(ISBLANK(Sheet1!F8), 0, 50) for a specific cell, but I want to query it for a name also.

Here's what I got so far and it's giving me an error: 

=IF(ISBLANK(QUERY(Sheet1!F, "select D where lower(B)='jennifer'") F) ,0, 50)

EDIT

I'll explain the use a bit more to avoid confusion.

Sheet1 is taking information from a Google Form being used for a points system. The form asks for first name, last name, if they ran or volunteered for an event that month. There are individual sheets for each person who submits the form. The sheet "Jennifer" is the monthly and total points for her.

What I'm trying to accomplish is check whether or not "Jennifer" participated in an event that month. The cell is either blank if they didn't, or has the event name in it if they did. If so, add 50 points for that month. Thus, if name="Jennifer" AND month="January" and cell is NOT blank, add 50 points.

Current code is below, has no errors, but gives unwanted results:

=IF(ISBLANK(QUERY(Sheet1!A1:G, "select F where lower(B)='jennifer'and E='"&B1&"'")),0,50)
1
google-sheetsspreadsheetgoogle-apps

1 Answers

0
votes

If I understand you correctly, you want to place 50 in a cell if the cell in column B is Jennifer OR if it is empty. IS this correct? If so, place this formula in the first cell you want the number to appear in. IF you place it in row 3, change the instances of B1:B to B3:B

=ARRAYFORMULA( IF(ISBLANK(B1:B),50, IF(LOWER(B1:B) = "jennifer", 50, 0)))

The ARRAYFORMULA applies this to the rows defined in the range in the test. In this case, B1 to B which is the last row.

I then test for a blank cell and return 50 if the cell is blank. If it is not blank, I do a test for jennifer in any combination of upper and lower case characters, and place 50 if the cell contains ONLY jennifer. If it has any other characters, it will place a 0 in the cell.

NOTE that this only works in Google Spreadsheets. It wil not work in an Excel spreadsheet as the ARRAYFORMULA() function does not exist there. Your title lists Google Sheets, but you also selected an Excel tag.

EDIT The QUERY() function appears to return an empty string, so the result is never blank. Change it to:

=IF("" = (QUERY(Sheet1!$A1:$G, "select F where lower(B)='"&A1&"'and E='"&B1&"'")),0,50)