How do I count?

[Sumit_Bhokare]

Hello All,

I need small help, On Sheet 1 - I have two columns both have dates, I need to count how many number of rows have difference >= 0.

I need this count on other Sheet 2 at any cell & I cant add column within Sheet1 to have differences between dates.

What formula will work in this case?

[#2]

@Sumit_Bhokare Not sure is this will work for you but something like in the formula below could set you off in the right direction.

=SUM(--(Sheet1!A1:A100>=Sheet1!B1:B100))

Note that this will count blank cells as well as these will be seen as equal to zero, and perhaps you need to swap columns A and B as I don't know the relationship between the two. Should A be greater than B or is it the other way around.

[#3]

Hi @Sumit_Bhokare 

here are two options:

For option 1, you would need a helper column that subtracts date 1 from date 2, so it calculates the difference.

Option 2 does work without a helper column.

Of course, you could put the formulas in a different sheet.

[#4]

@Sumit_Bhokare 

As @Riny_van_Eekelen solution

= SUM(SIGN(date1<=date2))

I also played with Lambda functions

= REDUCE(0, date1<=date2, COUNTIFλ)

"COUNTIFλ"
=LAMBDA(count,criterion,count+criterion)

where the function counts TRUE.  Sledgehammers and nuts come to mind.

[#5]

Is there any way to exclude blank cells count?

[#6]

@Sumit_Bhokare Then you need to think in terms like this:

=SUMPRODUCT(--(A3:A5>=B3:B5),--NOT(ISBLANK(A3:A5)),--NOT(ISBLANK(B3:B5)))

But perhaps better if you show us what you are working with. That avoids us from having to second guess your intentions.

[#7]

@Riny_van_Eekelen attached file for reference

[#8 · Accepted solution]

@Sumit_Bhokare See attached. Best to convert the data into a structured table. And just make sure there are no empty rows. Then my initial formula will work just fine.