Replace criteria range in countifs to be based on 2 cells date

[Drwats0n]

Hey guys, i want to countif a column if the dates are between and equals 1 march 22 and 31 march 22 and if the cell value between C:C is more than 300k. instead of changing C424 and C454 every month based on the latest dates column, how do i replace C424:C454 with just cell C459 and D459? Or i should use INDIRECT? I have attached the excel screenshot forr reference.

=COUNTIFS(C424:C454,">" &C467) --> it works but have to update C424 and C454 every month to keep up with the dates.

=COUNTIFS(C:C,">"&C467,A:A,">="&C459,A:A,"<="&D459) --> for some reason it doesnt work. Or countifs doesnt work with C:C and have to dictate the value?

C459 cell value = 1 March 22
D459 cell value = 31 March 22
C467 cell value = 300k
C424:C454 = Range of dates between 1 march 22 and 31 march 22(i have to update this value manually every month)

 

[Joe4]

Is the value in C467 really "300k" (which would NOT be a valid number), or is it 300,000?
Do you have a hidden column on our sheet, as your formulas reference columns A-D, but I only see 3 columns in your sheet example.
Is column B hidden?

 

[Drwats0n]

sorry for late reply. i have reattached the file. 300k = 300,000 in cell.

 

[Drwats0n]

basically i want to countifs those under date range 1 march to 31 march only and if the amount is more than 300k.
=COUNTIFS(C:C,">"&C467,A:A,">="&C459,A:A,"<="&D459) --> this doesnt work....im not sure why. Is there any other way to make it work?

 

[Joe4]

Because you have your formulas and the criteria you want to check for in columns C and D under your data, you CANNOT use complete column references in those formulas!
You will want to stop at the bottom of your data, like this:

=COUNTIFS(C1:C454,">=" & $C$467,A1:A454,">=" & $C$459,A1:A454,"<=" & $D$459)

 

[Drwats0n]

Because you have your formulas and the criteria you want to check for in columns C and D under your data, you CANNOT use complete column references in those formulas!
You will want to stop at the bottom of your data, like this:

=COUNTIFS(C1:C454,">=" & $C$467,A1:A454,">=" & $C$459,A1:A454,"<=" & $D$459)

thanks for your reply, appreciate it. Is there any other formula that i can use to count based on my criteria and there is no need for me to manually update the C1:C454 and A1:A454 every i add new data below it? for example now it stops at 31march22(A454). But i will soon need to add new rows to input in april dates..

 

[Joe4]

If you use the row JUST before you criteria starts, row 457 in your example, like this:

=COUNTIFS(C1:C457,">=" & $C$467,A1:A457,">=" & $C$459,A1:A457,"<=" & $D$459)

then when you insert new rows, as long as you insert them BEFORE row 457, the formula will automatically adjust and grow, so you shouldn't need to edit it.

 

[Drwats0n]

If you use the row JUST before you criteria starts, row 457 in your example, like this:

=COUNTIFS(C1:C457,">=" & $C$467,A1:A457,">=" & $C$459,A1:A457,"<=" & $D$459)

then when you insert new rows, as long as you insert them BEFORE row 457, the formula will automatically adjust and grow, so you shouldn't need to edit it.

thank you and appreciate it.

 

[Joe4]

You are welcome.