Count Today every 3rd column

[jsimpser]

Hi,
I am working with a spreadsheet where I need to count how many times yesterday's date is used in every 3rd column. There are multiple columns with dates, so I only need it from every 3rd column.the below expression works, but to enter it for the entire spreadsheet would take a long time as it goes until FO.

=COUNTIF(H6:H108,TODAY()-1)+COUNTIF(K6:K108,TODAY()-1)...

Anyone know how to do this in one formula instead of mutliple formulas added together?
Thanks!

 

[KRice]

Try this:

MrExcel20200520.xlsx
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S
1
2	Count
3	8
4
5
6								5/20/2020
7														5/20/2020
8
9											5/20/2020
10								5/20/2020									5/20/2020
11														5/20/2020			5/21/2020
12																	5/20/2020
13														5/20/2020
14
Sheet14

Cell Formulas
Range	Formula
A3	A3	=SUMPRODUCT((MOD(COLUMN(H1:T1)-COLUMN(G1),3)=1)*(H6:T14=TODAY()-1))

 

[jsimpser]

Thank you. Unfortunately, when I enter the formula listed and adjust the cells for my spreadsheet I get an answer of 0 when it should be 6

 

[KRice]

Where is the upper left of your data block for counting...H6? and it runs through FO108?

If so, then this should work:
=SUMPRODUCT((MOD(COLUMN(H1:FO1)-COLUMN(G1),3)=1)*(H6:FO108=TODAY()-1))
I should have clarified too, this formula assumes that column H is the first column containing a date that you want to count...so H, K, N, etc will be counted. If this isn't correct, please clarify.

If it doesn't check the type of one of the dates that is not being counted. For example =TYPE(H8) and see if it reports back that it is a number (1) rather than text (2).

 

[jsimpser]

I tried this resulting in the same 0. When checking the type it comes back as a number.

You are correct, it has data from H6 to AO108 currently with expansion coming.

Is there a way in the formula to have the number date converted to text date or counted as a number date instead of a text date?

 

[KRice]

You mentioned in your first post that a long version of the formula was working. That version included the COUNTIF function used like this:
=COUNTIF(H6:H108,TODAY()-1)

TODAY() returns today's date, which is a number, so I'm assuming that if the COUNTIF function was generating an expected result, then using this:
(H6:FO108=TODAY()-1)
inside the SUMPRODUCT should also work. It works in my mock-up worksheet.
Could you copy the formula from your worksheet and paste it directly into a post here, please? I'm hesitant to begin inserting functions that convert numbers to text or vice versa without understanding the issue. To clarify, when you used the TYPE function on one of the dates that is not being counted, the function returned a 1, or did it return a 2?

Importantly, which column has the first value of TODAY()-1 that you want to count?

 

[jsimpser]

For the type, it returned a 1
The today function first column is H.

My function is:

=SUMPRODUCT((MOD(COLUMN(H1:AO1)-COLUMN(G1),3)=1*(H6:AO108=TODAY()-1)))

 

[KRice]

There is an issue with your parentheses...try this:
=SUMPRODUCT((MOD(COLUMN(H1:AO1)-COLUMN(G1),3)=1)*(H6:AO108=TODAY()-1))

 

[jsimpser]

When i try that it says there is a "missing an opening or closing parenthesis."

 

[KRice]

There are no missing or open parentheses. Simply select the entirety of the next line and paste it directly into your worksheet...
=SUMPRODUCT((MOD(COLUMN(H1:AO1)-COLUMN(G1),3)=1)*(H6:AO108=TODAY()-1))

Your formula in post #7 is incorrect in two places:
=SUMPRODUCT((MOD(COLUMN(H1:AO1)-COLUMN(G1),3)=1 [here you're missing a right parenthesis] * (H6:AO108=TODAY()-1)) [ ) here you've inserted this right parenthesis in the wrong place]

The suggested solution broken down into parts reduces to this:
=SUMPRODUCT( X )
X = ( MOD( Y - Z , 3 )=1 ) * ( U )
Y = COLUMN(H1:AO1)
Z = COLUMN(G1)
U = H6:AO108=TODAY()-1
You can count all left parentheses and see that they correspond to the same number of right parentheses.