SUMPRODUCT(--(MOD(OFFSET(C1,COUNTA(Dynamic Range),2)=1)) - Too few arguments - Why?

[Poker Joe]

I need to enter a formula that will automatically update as I enter data daily. I'm attempting to use a dynamic range within the formula.

The formula in "I2" will find how many odd numbers are in the data (rows of data in C:G) for the last 5 days but I have to adjust the range every time I enter new data.

This is my attempt for a dynamic range within the formula in I2, =SUMPRODUCT(--(MOD(OFFSET(C1,COUNTA(C2:G1500,0,-5,1),2)=1))

Microsoft gives an error message that I've entered too few arguments. Once I select "OK" on the error message, the last "1" in the formula bar is highlighted.

I'm lost. Thanks in advance to anyone that can assist.

Book1.xlsx
	A	B	C	D	E	F	G	H	I	J
1	ID	Date	Team1	Team2	Team3	Team 4	Team5		Odd	Even
2	1	44927	1	6	3	3	8	Last 5 Days	=SUMPRODUCT(--(MOD(C17:G21,2)=1))	=25-I2
3	2	44928	2	7	4	4	9
4	3	44929	3	8	5	7	3	Last 5 Days
5	4	44930	4	9	6	8	4	Dynamic Range	=SUMPRODUCT(--(MOD(OFFSET(C1,COUNTA(C2:G1500,0,-5,1),2)=1))
6	5	44931	5	10	1	9	5		You've entered too few arguments for this function
7	6	44932	3	8	5	7	4		After selecting OK on the Microsoft Error message, it highlights the last
8	7	44933	4	9	6	3	1		"1" in the formula.
9	8	44934	7	3	4	9	6
10	9	44935	8	4	5	10	1
11	10	44936	9	5	3	8	5
12	11	44937	10	6	4	9	6
13	12	44938	8	4	9	6	8
14	13	44939	3	8	5	7	3
15	14	44940	4	9	6	3	4
16	15	44941	7	3	4	9	5
17	16	44942	8	4	5	10	6
18	17	44943	2	7	4	4	9
19	18	44944	3	8	5	7	3
20	19	44945	4	9	6	8	4
21	20	44946	5	10	1	9	5
22
23
Sheet1

Cell Formulas
Range	Formula
I2	I2	=SUMPRODUCT(--(MOD(C17:G21,2)=1))
J2	J2	=25-I2

 

[RoryA]

You're missing a closing bracket to COUNTA(C2:G1500)

 

[Poker Joe]

You're missing a closing bracket to COUNTA(C2:G1500)

Doh! Those silly brackets get me every time.

That solves the error BUT my formula doesn't return the value I need. It returns "0" and should be 12.

Now, I'm really lost. Why? Why? Why?

Book1.xlsx
	A	B	C	D	E	F	G	H	I	J
1	ID	Date	Team1	Team2	Team3	Team 4	Team5		Odd	Even
2	1	1/1/2023	1	6	3	3	8	Last 5 Days	12	13
3	2	1/2/2023	2	7	4	4	9
4	3	1/3/2023	3	8	5	7	3	Last 5 Days
5	4	1/4/2023	4	9	6	8	4	Dynamic Range	0
6	5	1/5/2023	5	10	1	9	5		You've entered too few arguments for this function
7	6	1/6/2023	3	8	5	7	4		After selecting OK on the Microsoft Error message, it highlights the last
8	7	1/7/2023	4	9	6	3	1		"1" in the formula.
9	8	1/8/2023	7	3	4	9	6
10	9	1/9/2023	8	4	5	10	1
11	10	1/10/2023	9	5	3	8	5
12	11	1/11/2023	10	6	4	9	6
13	12	1/12/2023	8	4	9	6	8
14	13	1/13/2023	3	8	5	7	3
15	14	1/14/2023	4	9	6	3	4
16	15	1/15/2023	7	3	4	9	5
17	16	1/16/2023	8	4	5	10	6
18	17	1/17/2023	2	7	4	4	9
19	18	1/18/2023	3	8	5	7	3
20	19	1/19/2023	4	9	6	8	4
21	20	1/20/2023	5	10	1	9	5
22
23
Sheet1

Cell Formulas
Range	Formula
I2	I2	=SUMPRODUCT(--(MOD(C17:G21,2)=1))
J2	J2	=25-I2
I5	I5	=SUMPRODUCT(--(MOD(OFFSET(C1,COUNTA(C2:G1500),0,-5,1),2)=1))

 

[Fluff]

How about

=SUMPRODUCT(--(MOD(OFFSET(C1,COUNTA(C2:C1500),0,-5,5),2)=1))

 

[Poker Joe]

How about

=SUMPRODUCT(--(MOD(OFFSET(C1,COUNTA(C2:C1500),0,-5,5),2)=1))

That worked beautifully.

I did try to put that formula in another worksheet and change the OFFSET starting cell to C9,

=SUMPRODUCT(--(MOD(OFFSET(C9,COUNTA(C10:C1500),0,-5,5),2)=1))

because C1:C8 are blank and C9 is a header cell with text. It gave me a value 10 less than the value it should be. When I change the OFFSET starting cell to C1, and the COUNTA range to (C2:C1500) as you suggested for the other worksheet that I posted, it worked correctly.

I thought with OFFSET my start cell could be anywhere, then select the range. I see how you expanded the COUNTA range with "-5,5" vs. me having the range "C2:G1500". I need to review OFFSET and COUNTA again. I missed or assumed something.

Thank You! You're an Excel Hero for sure.

 

[Fluff]

Your formula starting in C9 should work assuming you don't have any empty cells in the data & that you don't have cell with "" below the data.

 

[Poker Joe]

Your formula starting in C9 should work assuming you don't have any empty cells in the data & that you don't have cell with "" below the data.

Hmmmm...I selected C9, Ctrl + Shift to the bottom of my data, then Find & Select, Go To Special, Blank Cells, No Cells Were Found.

Then did Find, "", No Cells were Found.

I expanded the range to include all of my data range, searched Blank Cells and Find "", didn't Find anything.

The data does have Formatting, Cell Fill, Bold Text, Colored Text on some of the cells but I don't think that would have any effect on the execution of the formula.

The formula you gave me works fine with C1 as the starting cell. I'm just curious why it won't with C9. As long as it works I'm good but it does drive me crazy not to know why it won't work the way I thought it should. I appreciate your help. It's not mystery that I have to solve right now. If I find something, I'll reply with what I found.

I have another formula, COUNTIF, with dynamic range that's not working correctly but I'll start another thread for that one.

Thank You!

 

[Fluff]

If from C9 you do Ctrl down arrow does it take you the last row of data?
If so do it again from where you end up & does that take you to row 1,048,576

 

[Poker Joe]

It does, 1048576.

I don't know if this has any bearing but I'm sure it does. I have a COUNTIF formula that I added the same dynamic range for range. It works fine in my sample worksheet but in the worksheet I need it in, it counts the "22" in the criteria of ,"<"&22.

=COUNTIF(C1364:G1368,"<"&22)

Returns correct value of 12

=COUNTIF(OFFSET(C1,COUNTA(C2:C1500),0,-5,5),"<"&22)

Returns incorrect value of 14

It's counting "22" as = value, not less than.

Last 5 rows:

NC5AnalysisSummaryDP2.xlsm
	C	D	E	F	G
1364	5	14	22	29	42
1365	16	24	30	36	41
1366	19	20	21	22	36
1367	1	2	16	18	30
1368	4	12	31	36	42
DB SUM

Something is janky with the worksheet, one cell in the range or multiple cells but I can't figure out what. I've macros that I run through the data to change Color Fill/Bold/Text Color depending on different criteria but nothing in the macro that adds text.

I've stripped all formatting, though I knew that should have no effect, it didn't. The cells in the data are all General. I copied/multiplied all the data cells with "1". Got no love.

 

[Poker Joe]

=SUMPRODUCT(--(MOD(OFFSET(C9,COUNTA(C10:C1500),0,-5,5),2)=1))

The above formula works. I honestly don't know what I was doing wrong that resulted in it not working earlier but after getting some sleep and trying again, it works. The above formula works perfectly as suggested by Fluff.
Sorry Fluff for not being able to make it work earlier.
You were helpful but I was having an idiot day.

I did download the data again and copy/pasted it into the same worksheet and all is well. I just don't know, but it works great now.