Countif problem (with formule inside)

I have a short question where I am struggling with for a while:

I have a formule with countifs:
I want to count if:

Count if the seperate values in column G minus K45 are bigger than 30.

I tried a lot of things like the option below: (I know bit stupid option)
=COUNTIF(ALL!$G:$G;"-k45"&">=30")

Hope someone can help me!

Thanks a lot

Hi

You can use a helper and hidden column to do easy this.

Let's say in H2 & copy down put this.

=G2-$K$45

Then use simple countif.

=COUNTIF(H:H,">30")

Hi Kies386,

Please share a sample workbook. Thanks.

Regards,
DILIPandey

<click on below 'star' if this helps>

try
=SUMPRODUCT(--(G1:G5000-(ROW(G1:G5000)/ROW(G1:G5000)*K45)>30))

Excellent way of thinking, Martin!

what are you counting,you cant count dates like that ,all dates since 31/01/1900 are >30

Please don't duplicate threads.. continue in one thread only!

for the data you have its every cell in g - n2
=SUMPRODUCT(--((ROW(G2:G74)/ROW(G2:G74)*N2)-G2:G74>=30))
or seeing your other thread
=SUMPRODUCT(--(N2-$G$2:$G$74>=30)) =26

hmm
=SUMPRODUCT(--(ABS($G$2:$G$74-N2)>30))=25
=SUMPRODUCT(--(ABS($G$2:$G$74-N2)>=30)) =27
and i learnt something too !

for the data you have its every cell in g - n2
=SUMPRODUCT(--((ROW(G2:G74)/ROW(G2:G74)*N2)-G2:G74>=30))

Since your other thread was closed, my offering, based on my understanding was:

=SUMPRODUCT(--(ABS($G$2:$G$74-N2)>30))


copied across...

Create a Dynamic Named Range.

Go to Formula tab, Define Name and enter a name like: TradeDates

Then enter formula:

=Sheet1!$G$2:INDEX(Sheet1!$G:$G,COUNT(Sheet1!$G:$G)+1)

Click Ok.

Now replace the G range(s) in your formula with TradeDates.

instead of Dynamic Ranges, see if you can work with the Table feature, which automatically expands upon addition of data. highlight the cell TRADE DATE (cell G1) in your sheet. then Insert > Table (keyboard shortcut ALT > N > T). make sure to confirm the entire range is showing, including the header cell. keep the checkbox checked on and click on OK. if it is the first table in the workbook, it should be named Table1, but you can confirm that by going to Formulas > Name Manager (ALT > M > N). use the table's name in your formula. whenever you add a new value to the table, the table will automatically expand and the formula is be updated.

for e.g., NBVC's formula would become:

=SUMPRODUCT(--(ABS(Table1-N2)>30))