CODEWITHSUNDEEP
excelsumgoogle-sheetscountifarray-formulas
b3ck
b3ck

Reputation: 97

Google Sheets: Using ArrayFormula to COUNTIF depending on DATE in RANGE

Okay so I have two formulas I would like to use together, one I use to 'SUM' the total of projects from the day before:

=ArrayFormula(SUM(1*(INT(Archive!M3:M)=Today()-1)))

This will count all projects done from the day before.

Now I have several different types of projects, I use the following formula to separate project types and give a grand total: 

=COUNTIF('V1 Archive'!D:D,"25A")

What I would like to do is be able to 'SUM' the total of a specific project from the day before.

A few more details:

The data is plain text project codes ex; 25A, 25B, 25C etc.. The date is formatted like; 6/3/2014 16:41:36

Upvotes: 0

Views: 2847

Answers (1)

N8sBug
N8sBug

Reputation: 352

Before I answer you question directly, it should be noted that COUNTIFS() is available in new Google Spreadsheets and this formula is much easier.

However, you specifically asked how would one do this using ARRAYFORMULA().

First, let's decompose an ARRAYFORMULA():

I will use the following data for this found on a sheet called: Archive

enter image description here

Let's say I do the following on the Master sheet: =Arrayformula(Int(Archive!B$1:B$10))

I end up with the following being output. Note I DID NOT drag down. Google spreadsheets does this automatically, because the formula returns an entire column rather than performing a cell by cell function

[insert array formula output]

Now that you see the arrayformula() concept, we can build up your formula.

First, for your criteria you have: It must be completed yesterday (criteria 1), and It must equal a certain project (criteria 2).

Using that we will compose a formula that will find the intersection of these. A logical thought would be to use 'AND()' in an if statement, but that won't work because it will attempt to find the logical intersection (logical AND) of the entire set.

So we are going to take advantage of the "any number times 0 is 0"-result and make all things that we want for one criteria be a 1 in one arrayformula and all the things for the other criteria be a 1 in a separate arrayformula, like so: criteria 1: =arrayformula(if(int(Archive!B$1:B)=Today()-1,1,0)) criteria 2: =arrayformula(if(Archive!A$1:A=A2,1,0)) (Note: In this formula, I made a look up table in the A column to compare against, ie "A2".)

Then we will use sumproduct() to combine them.
=sumproduct(arrayformula(if(int(Archive!B$1:B)=Today()-1,1,0)),arrayformula(if(Archive!A$1:A=A2,1,0)))

The following is the output on the master screen.

enter image description here

Upvotes: 2

Related Questions