# CSE formula with dates

#### pbolon

##### New Member
I have two columns of data and I want to count the number of times where condition1 AND condition2 are true. Column 1 contains dates in dd.mm.yyyy format, column 2 contains text. I am looking for the occurences of "accepted" that occur before a specific date.

=SUM(IF((D25:D32="accepted")*(A25:A32<"01.02.2002");1;0))

does not work nor does

=SUM(IF(D25:D32="accepted",IF(A25:A32<"01.02.2002",1,0),0))

any ideas?
-P

### Excel Facts

What did Pito Salas invent?
Pito Salas, working for Lotus, popularized what would become to be pivot tables. It was released as Lotus Improv in 1989.
A couple of quick questions:
• How does cell A25 appear (05.22.2002)
• What number appears if you change the format to general

On 2002-08-28 11:35, pbolon wrote:
I have two columns of data and I want to count the number of times where condition1 AND condition2 are true. Column 1 contains dates in dd.mm.yyyy format, column 2 contains text. I am looking for the occurences of "accepted" that occur before a specific date.

=SUM(IF((D25:D32="accepted")*(A25:A32<"01.02.2002");1;0))

does not work nor does

=SUM(IF(D25:D32="accepted",IF(A25:A32<"01.02.2002",1,0),0))

any ideas?
-P

Before the site went down, I jotted down the following:

Why would you choose for such a foreign date format (which I assume to be text) while you want to compute with them?

See if this works:

=SUMPRODUCT((SUBSTITUTE(A25:A32,".","/")+0 < SUBSTITUTE("01.02.2002",".","/")+0)*(D25:D32="accepted"))

which is normally entered.

Caveat. There should be no blank cells in A25:A32.

Otherwise, use the following array formula:

=SUM(IF((D25:D32="accepted")*(SUBSTITUTE(A25:A32,".","/")+0<=SUBSTITUTE("01.02.2002",".","/")+0),1,0))

To array enter a formula, you need to hit control+shift+enter at the same time, not just enter.

I'd like also to see the answer to IML's questions.

On 2002-08-28 12:18, IML wrote:
A couple of quick questions:
• How does cell A25 appear (05.22.2002)
• What number appears if you change the format to general

since this is a german version of excel, the format is dayday.monthmonth.yearyearyearyear. If I enter 28.08.2002 (Aug 28th, 2002) and change to general I get 37497.
This message was edited by pbolon on 2002-08-28 23:57

On 2002-08-28 13:50, Aladin Akyurek wrote:
On 2002-08-28 11:35, pbolon wrote:
I have two columns of data and I want to count the number of times where condition1 AND condition2 are true. Column 1 contains dates in dd.mm.yyyy format, column 2 contains text. I am looking for the occurences of "accepted" that occur before a specific date.

=SUM(IF((D25:D32="accepted")*(A25:A32<"01.02.2002");1;0))

does not work nor does

=SUM(IF(D25:D32="accepted",IF(A25:A32<"01.02.2002",1,0),0))

any ideas?
-P

Before the site went down, I jotted down the following:

Why would you choose for such a foreign date format (which I assume to be text) while you want to compute with them?

See if this works:

=SUMPRODUCT((SUBSTITUTE(A25:A32,".","/")+0 < SUBSTITUTE("01.02.2002",".","/")+0)*(D25:D32="accepted"))

which is normally entered.

Caveat. There should be no blank cells in A25:A32.

Otherwise, use the following array formula:

=SUM(IF((D25:D32="accepted")*(SUBSTITUTE(A25:A32,".","/")+0<=SUBSTITUTE("01.02.2002",".","/")+0),1,0))

To array enter a formula, you need to hit control+shift+enter at the same time, not just enter.

I'd like also to see the answer to IML's questions.

This started as somebody else's database and I am trying to extract info from it. I could change all the dates to numbers and then the comparison works fine, I was just hoping there was a way to work with dates since there is utility in keeping it in a date format in terms of date mathematics

On 2002-08-28 23:55, pbolon wrote:
On 2002-08-28 13:50, Aladin Akyurek wrote:
On 2002-08-28 11:35, pbolon wrote:
I have two columns of data and I want to count the number of times where condition1 AND condition2 are true. Column 1 contains dates in dd.mm.yyyy format, column 2 contains text. I am looking for the occurences of "accepted" that occur before a specific date.

=SUM(IF((D25:D32="accepted")*(A25:A32<"01.02.2002");1;0))

does not work nor does

=SUM(IF(D25:D32="accepted",IF(A25:A32<"01.02.2002",1,0),0))

any ideas?
-P

Before the site went down, I jotted down the following:

Why would you choose for such a foreign date format (which I assume to be text) while you want to compute with them?

See if this works:

=SUMPRODUCT((SUBSTITUTE(A25:A32,".","/")+0 < SUBSTITUTE("01.02.2002",".","/")+0)*(D25:D32="accepted"))

which is normally entered.

Caveat. There should be no blank cells in A25:A32.

Otherwise, use the following array formula:

=SUM(IF((D25:D32="accepted")*(SUBSTITUTE(A25:A32,".","/")+0<=SUBSTITUTE("01.02.2002",".","/")+0),1,0))

To array enter a formula, you need to hit control+shift+enter at the same time, not just enter.

I'd like also to see the answer to IML's questions.

This started as somebody else's database and I am trying to extract info from it. I could change all the dates to numbers and then the comparison works fine, I was just hoping there was a way to work with dates since there is utility in keeping it in a date format in terms of date mathematics

Are you saying that:

=SUMPRODUCT((SUBSTITUTE(A25:A32,".","/")+0 < SUBSTITUTE("01.02.2002",".","/")+0)*(D25:D32="accepted"))

does not work? If not, what do you get? BTW, you need to replace the list separator "," with ";" in your version of Excel.

On 2002-08-29 01:00, Aladin Akyurek wrote:
On 2002-08-28 23:55, pbolon wrote:
On 2002-08-28 13:50, Aladin Akyurek wrote:
On 2002-08-28 11:35, pbolon wrote:
I have two columns of data and I want to count the number of times where condition1 AND condition2 are true. Column 1 contains dates in dd.mm.yyyy format, column 2 contains text. I am looking for the occurences of "accepted" that occur before a specific date.

=SUM(IF((D25:D32="accepted")*(A25:A32<"01.02.2002");1;0))

does not work nor does

=SUM(IF(D25:D32="accepted",IF(A25:A32<"01.02.2002",1,0),0))

any ideas?
-P

Before the site went down, I jotted down the following:

Why would you choose for such a foreign date format (which I assume to be text) while you want to compute with them?

See if this works:

=SUMPRODUCT((SUBSTITUTE(A25:A32,".","/")+0< SUBSTITUTE("01.02.2002",".","/")+0)*(D25:D32="accepted"))

which is normally entered.

Caveat. There should be no blank cells in A25:A32.

Otherwise, use the following array formula:

=SUM(IF((D25:D32="accepted")*(SUBSTITUTE(A25:A32,".","/")+0<=SUBSTITUTE("01.02.2002",".","/")+0),1,0))

To array enter a formula, you need to hit control+shift+enter at the same time, not just enter.

I'd like also to see the answer to IML's questions.

This started as somebody else's database and I am trying to extract info from it. I could change all the dates to numbers and then the comparison works fine, I was just hoping there was a way to work with dates since there is utility in keeping it in a date format in terms of date mathematics

Are you saying that:

=SUMPRODUCT((SUBSTITUTE(A25:A32,".","/")+0< SUBSTITUTE("01.02.2002",".","/")+0)*(D25:D32="accepted"))

does not work? If not, what do you get? BTW, you need to replace the list separator "," with ";" in your version of Excel.

Brilliant.

=SUMPRODUCT((SUBSTITUTE(A25:A32,".","/")+0< SUBSTITUTE("01.02.2002",".","/")+0)*(D25:D32="accepted"))

or I should say

=SUMMENPRODUKT((WECHSELN(A25:A32;".";"/")+0<WECHSELN("01.02.2002";".";"/")+0)*(D25:D32="accepted"))

works perfectly. don't understand why it works though
This message was edited by pbolon on 2002-08-29 03:33
This message was edited by pbolon on 2002-08-29 03:34

On 2002-08-29 03:32, pbolon wrote:
On 2002-08-29 01:00, Aladin Akyurek wrote:
On 2002-08-28 23:55, pbolon wrote:
On 2002-08-28 13:50, Aladin Akyurek wrote:
On 2002-08-28 11:35, pbolon wrote:
I have two columns of data and I want to count the number of times where condition1 AND condition2 are true. Column 1 contains dates in dd.mm.yyyy format, column 2 contains text. I am looking for the occurences of "accepted" that occur before a specific date.

=SUM(IF((D25:D32="accepted")*(A25:A32<"01.02.2002");1;0))

does not work nor does

=SUM(IF(D25:D32="accepted",IF(A25:A32<"01.02.2002",1,0),0))

any ideas?
-P

Before the site went down, I jotted down the following:

Why would you choose for such a foreign date format (which I assume to be text) while you want to compute with them?

See if this works:

=SUMPRODUCT((SUBSTITUTE(A25:A32,".","/")+0< SUBSTITUTE("01.02.2002",".","/")+0)*(D25:D32="accepted"))

which is normally entered.

Caveat. There should be no blank cells in A25:A32.

Otherwise, use the following array formula:

=SUM(IF((D25:D32="accepted")*(SUBSTITUTE(A25:A32,".","/")+0<=SUBSTITUTE("01.02.2002",".","/")+0),1,0))

To array enter a formula, you need to hit control+shift+enter at the same time, not just enter.

I'd like also to see the answer to IML's questions.

This started as somebody else's database and I am trying to extract info from it. I could change all the dates to numbers and then the comparison works fine, I was just hoping there was a way to work with dates since there is utility in keeping it in a date format in terms of date mathematics

Are you saying that:

=SUMPRODUCT((SUBSTITUTE(A25:A32,".","/")+0< SUBSTITUTE("01.02.2002",".","/")+0)*(D25:D32="accepted"))

does not work? If not, what do you get? BTW, you need to replace the list separator "," with ";" in your version of Excel.

Brilliant.

=SUMPRODUCT((SUBSTITUTE(A25:A32,".","/")+0< SUBSTITUTE("01.02.2002",".","/")+0)*(D25:D32="accepted"))

or I should say

=SUMMENPRODUKT((WECHSELN(A25:A32;".";"/")+0<WECHSELN("01.02.2002";".";"/")+0)*(D25:D32="accepted"))

works perfectly. don't understand why it works though
This message was edited by pbolon on 2002-08-29 03:33
This message was edited by pbolon on 2002-08-29 03:34

See the following on SUMPRODUCT if you want to know how this function works:

http://www.mrexcel.com/wwwboard/messages/8961.html

Replies
1
Views
289
Replies
0
Views
237
Replies
0
Views
188
Replies
4
Views
166
Replies
2
Views
247

1,196,250
Messages
6,014,230
Members
441,808
Latest member
xplainer

### 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.

### Which adblocker are you using?

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

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