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

 

[Drew_]

Okay, I formatted it as a number and I put the formula in C2:C61 and it returned 1 for every cell. I think because there are formulas in cells B2:B61. Those formulas are:

=IF(INDIRECT(F1&"!B2")="-","",INDIRECT(F1&"!B2"))

When I took out the IF part of the formula, it returned: - and your formula still returned 1

 

[Jonmo1]

Then how does your original formula work in Excel using the same subtotal ?


Maybe I've misunderstood your purpose of using subtotal in the original formula.
Is that meant to make the formula not count rows that are hidden ?

 

[Drew_]

Yes. So the INDIRECT formula will return text values and when I filter the column, a normal SUBTOTAL function will return a negative number (i.e. there's 2 entries and I filter it to just 1, I get -57). The SUBTOTAL is counting all the blank cells when I filter the column and I don't want it to do that. I only want it count the visible cells.

 

[Jonmo1]

Ok, so the original formula actually does NOT work in Excel, you had said it works great in Excel. I was working on that assumption.

You need to make the subtotal function refer to a column that does not use formula, but will always be populated in the rows you want to count.

 

[Drew_]

I can send you my Excel file if you want to see, it does work. Couldn't tell you how, but it does

Okay I will create another column and do it that way, I'll let you know what I come up with. Thanks for your help

 

[Jonmo1]

You just said it doesn't work the way you want it to

The SUBTOTAL is counting all the blank cells when I filter the column and I don't want it to do that.

And why are you sticking your toung out at me?

 

[Drew_]

Yes, I was saying the SUBTOTAL function does not work in Google Sheets but that it works fine in Excel. I was making fun of myself with the tongue thing.

I put the following formula in the C2 and copied it down: =IF(B2="","",1) and then in B62 I put: =SUBTOTAL(109,C2:C61) and it works great when I filter the B column. Thank you for your suggestions and help

 

[Jonmo1]

You're welcome.

But I think I see my mistake..

the countifs would be this, assuming the values in B are expected to be text strings, not numbers
=COUNTIFS($C$2:$C$61,1,$B$2:$B$61,"?*")

And don't use =SUBTOTAL(109,C2:C61)
The subtotal should go in a helper column, say C2
=SUBTOTAL(103,B2)
And fill down.

 

[Jonmo1]

Example

Unknown
	B	C	D	E	F
1	Header	Header2	Header3		7
2	a	1	a
3	b	1	b
4	c	1	c
5		1
6	d	1	d
7	e	1	e
8		1
9		1
10	f	1	f
11	g	1	g
Sheet1

Cell Formulas
Range	Formula
F1	=COUNTIFS($C$2:$C$61,1,$B$2:$B$61,"?*")
B2	=IF(D2="","",D2)
B3	=IF(D3="","",D3)
C2	=SUBTOTAL(103,B2)
C3	=SUBTOTAL(103,B3)