Count

Excel Facts

When did Power Query debut in Excel?
Although it was an add-in in Excel 2010 & Excel 2013, Power Query became a part of Excel in 2016, in Data, Get & Transform Data.
Hi
I assume you are looking for an If statement.

=IF(AND(D1-C1>30,C1>A1,C1>A1), "true", "false")

Replace "true" with what you want to see in the cell if its true.
Replace "false" with what you want to see in your cell if its false

Hope this is what you are looking for
Derek
 
Upvote 0
Derek,

Not quite. I have time values in ABCD

10:00 10:40 10:10 10:41

It meets the criteria, but doesn't count as 1

Mahalo
 
Upvote 0
question - if you have time values in the cells, how will D1-C1>30 ever be true? am i missing something? do you have dates as well?
 
Upvote 0
oh, i see. i thought it would count that as .517. have never been good with time formats...thanks
 
Upvote 0
hang on. =D1-31 returns .31, not 31, so you will have amend the test condition from 30 to .30 (or will it be .5?)
 
Upvote 0
I tried formatting target cell to number, general, and time..no go. changed 31 to .31 and :31 doesn't count it ..
 
Upvote 0
On 2002-02-28 20:31, Brian from Maui wrote:
Aloha,

I would like to count these events as 1. Lost again :oops:

d1-c1>30
c1>a1
d1>b1

Mahalo

Brian,

Consider the following sample, where the funny numbers are Excel's internal representation of actual times,

{0.416666666666667,0.444444444444444,0.423611111111111,0.445138888888889;
0.40625,0.427083333333333,0.409722222222222,0.430555555555556;
0.427083333333333,0.447916666666667,0.430555555555556,0.456944444444444}

in A1:D3.

In E1 enter & copy down:

=((D1-C1)*1440>30)*(C1>A1)*(D1>B1)

You can then sum column E to get desired count:

=SUM(E:E)

or

=SUM(E1:E3)

However, you can also arrive at the desired count by means of a single formula:

In F1 enter:

=SUMPRODUCT(((D1:D3-C1:C3)*1440>30)*(C1:C3>A1:A3)*(D1:D3>B1:B3))

Aladin
This message was edited by Aladin Akyurek on 2002-03-01 00:35
 
Upvote 0

Forum statistics

Threads
1,213,527
Messages
6,114,142
Members
448,551
Latest member
Sienna de Souza

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top