Why isn't my formula working?

New Contributor

I am using the following formula:

 

=ARRAY_CONSTRAIN(ARRAYFORMULA(IFERROR(INDEX('Pick Your Meals'!$E$1:$BIF$1,SMALL(IF('Pick Your Meals'!$E$130:$BIF$130>0,COLUMN('Pick Your Meals'!$E$1:$BIF$1)-COLUMN($D$1)+1,""),ROWS($1:1))),"")), 1, 1)

 

What it should be doing is when the total cell on the sheet 'pick your meals' is populated with a number it returns the two header fields and total, if it's a zero in the total column it should return nothing, giving me a shopping list, see link below to doc, but it's returning random zero total columns

 

https://docs.google.com/spreadsheets/d/1BQyDfeGVVpAmoGlloaQ2WYFc3ilOdOUHcJMZ8AvdhJ0/edit?usp=sharing

2 Replies

@Emalou You are posting your question to an MS Excel forum. Perhaps better to look into one that specialises ib Google Sheets.

I don't use google sheets, but it appears it has a filter function. Perhaps try something like:

transpose(filter('Pick Your Meals'!E1:BIF2, 'Pick Your Meals'!E130:BIF130>0))

arrayformula(roundup(transpose(filter('Pick Your Meals'!E130:BIF130, 'Pick Your Meals'!E130:BIF130>0)), 0))