Google-Sheets : ARRAYFORMULA() for multiple columns

I have this table :

There are 3 Sheets inside it.

The idea is to get how many ingredients used each day. For example as in Sheet1 in day 1 :

cake1 uses 2 chocolate
cake2 uses 0 chocolate
cake3 uses 0 chocolate
cake4 uses 3 chocolate

So in day1, there are 5 chocolate used.

I use this formula in C3 :


Then i copy/paste to range C3:J7 and it works as i expected. Now, i want to make it as an arrayformula . So i modify it in Sheet2 :

=arrayformula( sumif($B$10:$B$28,$B3:$B7,C$10:C$28) )

it works for single column (in C3:C7 of the Sheet2 in link i share). I can then copy paste for other columns but i want to try to make arrayformula to works for the whole range C3:J7.

In Sheet3 , it populate the formula across the range C3:J7, but the value is wrong.

Can someone help if it’s possible to make my arrayformula to apply to multiple columns ?


enter image description here

Google-Sheets : How to flag duplicate items within the same ‘group’?

I have this table :
enter image description here

So i have the days column and fruit column. There’s grouping based on days : Monday, Tuesday , Wednesday etc indicated by that grey and light blue color to separate each day. The fruits are grouped by the days.

I want make a single conditional formatting (for J1:J range) to flag if there’s the same fruit on the same day. For example that red font color : 2 apple on Tuesday and 2 banana on Thursday.

How can i do this ?


google sheets – How to Run a Query in GoogleSheets for New 26-Week Highs 2 of Last 4 Days Using GOOGLEFINANCE Function

I already have a query that returns a list of 26-Week Highs (from a sheet with my list of stocks (Column A), column of today’s high (Column B), column of 26-Week High (Column C)). I’d like to have a query that returns a list of stocks that have hit new 26-Week highs twice in the last 4 days (does not have to have been today). Is there an eloquent way to do this with nesting queries?

I am unsure how to compare the list of stocks to see if two match in any of the four columns.

I am trying to nest queries with an if statement so:
IF(Return cells if they repeat in 2 of the 4 columns from the following queries)

=Query(Sheet2!A:I,”select * where B>=C”);
=Query(Sheet2!A:I,”select * where D>=E”);
=Query(Sheet2!A:I,”select * where F>=G”);
=Query(Sheet2!A:I,”select * where H>=I”)

I’m struggling with how to filter GoogleSheets by date via Google App Script


Via the code below, I’m able to pull the spreadsheet into a 2D array called ‘ssData’
I can see that my dates are in ssData()(5)
I want to filter for only rows where ‘EventDate’ equals ‘3/19/2021’
I only need columns(2,4, & 5) but not sure if that adds more complexity or is confusing the issue.

var spreadsheetId = ‘1TjNfOjcB7AAZKSaVySnuV-odnns6ClUxECkpSuebGek’;
var rangeName = ‘A2:O’;
var ssData = Sheets.Spreadsheets.Values.get(spreadsheetId, rangeName).values;

–the ask
Can anyone provide insights on next steps or good samples/examples for filtering spreadsheets(or 2d arrays by date) via Google App Script? the code that comes up on google searches is non-functioning and I can’t get them to work.
Thank You.

google sheets – Googlesheets pivot table won’t show totals for more than one Row item

I’ve got Totals checked or both Common Name and for Pot Description. Why won’t more than one display? If I move Pot description up, it will total then Common Name won’t.

If I untick Common Name totals, there is still no total.

If I delete Common Name, I still don’t get totals.

What am I missing?

screenshot 1

GoogleSheets Query ImportRange breaks if source sheet is blank, gives error: #N/A (Error Query completed with an empty output)

So I am importing data from 42 separate google sheets:
=QUERY({IMPORTRANGE(‘Reference Tables’!E3,’Reference Tables’!F3);
IMPORTRANGE(‘Reference Tables’!E4,’Reference Tables’!F4);
IMPORTRANGE(‘Reference Tables’!E4,’Reference Tables’!F5)},

I have reduced the above example to 3 sheets instead of all 42..!

Problem is that if any one of the 42 sheets are blank (which is likely to occur with several), then the whole QUERY breaks. I just want it import any sheets with data and ignore any without data..