INDEX formula not working when transferred to Google Sheets

[Drew_]

Hi all.

I've had great success on here and I didn't know where else to ask. I have a simple formula that works great in Excel but when I transfer the document to Google Sheets so I can share it, I get the following message:

=IF($E$6:$E$27=1,(INDEX($F$6:$F$27,0,1)),"-") returns #REF!: Array result was not expanded because it would overwrite data in G8.

I've tried INDEX and MATCH and I get answers, but not the correct ones. Essentially, all I want to say is "If anything in the E column equals 1, give me the value for the same row in the G column". I'm sure there's a workaround, just not positive what it is. Thanks

 

[Marcelo Branco]

Essentially, all I want to say is "If anything in the E column equals 1, give me the value for the same row in the G column". I'm sure there's a workaround, just not positive what it is. Thanks

You need a different formula.

Try
=IF(COUNTIF($E$6:$E$27,1),INDEX($G$6:$G$27,MATCH(1,$E$6:$E$27,0)),"-")

M.

 

[Drew_]

Unfortunately that formula does not work either. This is what my 2 columns look like:

<style type="text/css"><!--td {border: 1px solid #ccc;}br {mso-data-placement:same-cell;}--></style>

GIR	Putts
1	2
1	1
	1
1	2
	2
1	2
	2
1	2
1	2
6	16
1	1
	2
	1
1	2
	2
	2
1	2
1	2
1	1
5	15

<colgroup><col style="width: 98px"><col width="98"></colgroup><tbody>
</tbody>

GIR is in the E column and Putts is in the F column. My formula is in the G column, and I wanna know "every time the E column says 1, give me the number in the F column". Your formula returned 2 for every cell, not sure why

 

[Jonmo1]

That seems way overcomplicated for such a simple task.

Wouldn't you just use

=IF(E6=1,F6,"-")

Unknown
	E	F	G
5	GIR	Putts
6	1	2	2
7	1	1	1
8		1	-
9	1	2	2
10		2	-
11	1	2	2
Sheet1

Cell Formulas
Range	Formula
G6	=IF(E6=1,F6,"-")
G7	=IF(E7=1,F7,"-")
G8	=IF(E8=1,F8,"-")
G9	=IF(E9=1,F9,"-")
G10	=IF(E10=1,F10,"-")
G11	=IF(E11=1,F11,"-")

 

[Drew_]

Lol yep, that works. I guess when we create convoluted and massive spreadsheets sometimes we think we need complicated formulas for everything. Back to the fundamentals, thanks Jonmo

 

[Jonmo1]

You're welcome.

For what it's worth, I think in google sheets to do an array formula, it actually has a funciton called ArrayFormula instead of pressing CTRL + SHIFT + ENTER

=ArrayFormula(.....)

 

[Drew_]

Okay I have another question now. I am trying to SUBTOTAL a column that has formulas in it that will populate with text when entries are made. The column has 60 rows, and they are all empty as of now. My formula works great in Excel:

=SUMPRODUCT(SUBTOTAL(103,OFFSET($B$2,ROW($B$2:$B$61)-ROW($B$2),0)),1-($B$2:$B$61=""))

But again, when I put it in Google sheets it tells me the following error:

SUMPRODUCT has mismatched range sizes. Expected row count: 1. column count: 1. Actual row count: 60, column count: 1.

Any ideas?

 

[Jonmo1]

Put the Subtotal in it's own column, and use plain old countifs

In say C2 and filled to C61
=SUBTOTAL(103,B2)

Then use
=COUNTIFS($C$2:$C$61,1,$B$2:$B$61,"<>")

 

[Drew_]

I think I see what you mean, so I inserted another column and in C2 I typed in =SUBTOTAL(103,B2) and it returned: 12/31/1899. I have NO idea how it got that, considering B2 is empty. Basically I just want the SUBTOTAL to work when I filter the column, I would use the AGGREGATE function but it does not work in Google Sheets.

 

[Jonmo1]

Blanks are considered 0
12/31/1899 is 0 formatted as a date.
Format the cell you put the subtotal in as a number instead of a date.

The formula will only return either a 1 or 0.
0 if it's blank or hidden, 1 if it's not blank AND not hidden.