How to SUM the VLOOKUP results based on condition in other column using ARRAYFORMULA in google sheets?

enter image description here
enter image description here

I would like to sum all the costs based on the products purchased between specific dates.

=SUM(ARRAYFORMULA(IFERROR(VLOOKUP(‘Purchased List Table’!B2:B,’Pricing Table’!A2:B,2,0),””)))

With the formula above, I managed to sum all the prices of all products in the Purchased List Table instead of a certain timeframe.

How could I modify the formula in order to sum only the costs of products within specific timeframe (e.g.: between 9/3/2021 to 9/5/2021 only)?

Thank you.

Google Sheets-Why is my countif, weeknum arrayformula miscalculating for the 52nd week of the year but works fine in calculating other weeks?

In Google Sheets, I have an array formula with countif that counts attendance based on week number. But for some reason on the 52nd week of the year, it counts all the blank cells. The answer in the cell should be 0 since we do not have school that week, but instead it is in the thousands. I assume it is counting blank cells for some reason.
Can anyone fix my formula so it doesn’t go wacky on the 52nd week every year? See cell R3 for formula and R10 for miscalculation on spreadsheet linked below.
My formula:
=arrayformula(if(len(P3:P),countif(weeknum($E3:E,21),O3:O20),””))

Link to spreadsheet: https://docs.google.com/spreadsheets/d/1jvTkohGw9c-nDnjv_ZpGCHLTNJbfE2d0FwzZjjTurbw/edit?usp=sharing

Thanks in advance!!!

Google Sheets – assign a year and date rank in ARRAYFORMULA

I want to number production runs in my system based on a continual count for that year, which is then preceeded by the year (with duplicates sharing the same number). For example, if the data set was

1 Nov 20
1 Nov 20
1 Dec 20
1 Jan 21
1 Feb 21
1 Nov 20

Then the production run number would be as follows:

1 Nov 20    20.01
1 Nov 20    20.01
1 Dec 20    20.02
1 Jan 21    21.01
1 Feb 21    21.02
1 Nov 20    20.01

And I can’t for the life of me work out how to do that as an ARRAYFORMULA. Obviously ranking is pretty simple:

=ARRAYFORMULA(IF(A1:A<>"", RANK(A1:A, UNIQUE(A:A), 1), ""))

But that will give me a continual ranking (without re-starting every year); is there any way to do what I want in an arrayformula?

google sheets – How can I use ARRAYFORMULA with RANK?

I want to use ARRAYFORMULA with RANK in google spreadsheet but I have no idea about it.

Here’s example

A B C D
1 X Y Z Rank of X
2 4 6 1 2
3 1 3 2 3
4 3 2 1 1

In this case, I can write expression =RANK(A2, A2:C2) and copy it for other cell (D2:D).

But I want to make column D automatically added when I add new data for XYZ.

So I first tried using ARRAYFORMULA like this expression for D2;
=ARRAYFORMULA(RANK(A2:A, A2:C))

And it doesn’t work very well because RANK function’s second parameter already could receive cell range.

Is there any way to make this example work?

Thank you

google sheets – ArrayFormula row by row discount calculation

The discount in question is a fixed percentage, the numbers to be reduced by said percentage is in the column to the immediate left. I’ve tried this in D5:

=ARRAYFORMULA(C5:C27 - (INDIRECT("RC(-1)",FALSE) * TO_PURE_NUMBER(D4)))

Where C5:C27 are the original numbers and the indirect bit just grabs the cell to the left with D4 being the percentage.

My problem is it grabs the cell to the left.. of the first row only so every number gets discounted by the same exact amount.

I am using Google Sheets.

google sheets – If there anyway i can check if the cell contains the result of a arrayformula()?

For example i have this in A1 :

=arrayformula(B1:B100)

If in A10 i accidentally enter a value (which will overlap with arrayformula result) , it will produce error in the A1 because of existing value. But is there anyway if i want to flag this cell A10 with conditional formatting?

For example if i enter ” ” (space) , all the cells will be blank without me knowing which one is has problem, except if i check error notification. So in big sheet matrix it’s faster for me to spot by coloring the cell.

How can i tell if the A10 has a non-formula value or the result of arrayformula() in A1 ?

isformula() only works if there’s a formula in A10 not as as the result of arrayformula().

google sheets – vlookup with multiple output and combining filter() with arrayformula()

I have 2 sheets : shot and deadline.

This is my shot sheet :

enter image description here

and this is the deadline sheet :
enter image description here

So in the deadline sheet, it’s based on the dates. It will pull the shotid from shot sheet.

in A2 of deadline sheet , i use :

=ifna( filter(shot!$A$2:$A, shot!$B$2:$B=A$1) ,)

Then i copy the formula to B2:2. The image2 above is the ideal output i’m looking for. The formula works fine, but i want to make a single arrayformula().

My first trial to use arrayformula() with the previous filter() :

=arrayformula( ifna( filter(shot!$A$2:$A, shot!$B$2:$B=A$1:$1) ,))

and it returns error. Filter() doesn’t work together with arrayformula. So i second trial is using vlookup :

=arrayformula( ifna( vlookup( A$1:$1,{ shot!$B$2:$B, shot!$A$2:$A },2,0) ,))

it will give me only 1 output per column. The vlookup only find the first match :
enter image description here

Can anyone help me ? is it possible to use arrayformula() for this case ?