FOR YOGI and ALADYN - YOUR'E RIGHT.....

[Eddie G.]

Yogi, You are right, this is what I am looking for but the formula that Aladyn provided only counts 5 and the answer should be six as illustrated in the original post:
http://mrexcel.com/board/viewtopic.php?topic=4021&forum=2&5

below is your response. You explained what I am trying to do correctly. I explained it poorly in my original post..... help!!


Eddie,

If I've read this correctly you want the count of any times in either A or B BUT NOT both, so:

A no time and B no time = 0
A time and B no time = 1
A no time and B time = 1
A time and B time ALSO = 1

if this is the case then Aladin's formula is the one to go with.

 

[Barrie Davidson]

On 2002-04-04 14:37, Eddie G. wrote:
Yogi, You are right, this is what I am looking for but the formula that Aladyn provided only counts 5 and the answer should be six as illustrated in the original post:
http://mrexcel.com/board/viewtopic.php?topic=4021&forum=2&5

below is your response. You explained what I am trying to do correctly. I explained it poorly in my original post..... help!!


Eddie,

If I've read this correctly you want the count of any times in either A or B BUT NOT both, so:

A no time and B no time = 0
A time and B no time = 1
A no time and B time = 1
A time and B time ALSO = 1

if this is the case then Aladin's formula is the one to go with.

Eddie, I tried Aladin's formula and it worked fine for me. Is your data exactly as posted at
http://mrexcel.com/board/viewtopic.php?topic=4021&forum=2&5

 

[Eddie G.]

Barrie, You are right, it works in that case but it doesn't work in the following case, the answer i want is 4 but aladyn's formula results in 3........

col a col b
1 1769
2 1748 1784
3 1755
4 1777

 

[Eddie G.]

well, it appears that the forum is not showing up the way i am typing it....the bottom line is that the count in col a may or may not be more than col b....i need the count of the number of rows that contain either a value in A or B or both as one.

 

[Ian Mac]

On 2002-04-04 14:37, Eddie G. wrote:
Yogi, You are right, this is what I am looking for but the formula that Aladyn provided only counts 5 and the answer should be six as illustrated in the original post:
http://mrexcel.com/board/viewtopic.php?topic=4021&forum=2&5

below is your response. You explained what I am trying to do correctly. I explained it poorly in my original post..... help!!


Eddie,

If I've read this correctly you want the count of any times in either A or B BUT NOT both, so:

A no time and B no time = 0
A time and B no time = 1
A no time and B time = 1
A time and B time ALSO = 1

if this is the case then Aladin's formula is the one to go with.

Yogi NOTHING, I wrote this response!!!???? Ian Mac
Hope you got your answer.

Barrie if you have time I have a nice VBA problem I'd like to present to you, may I E.mail???

 

[Ian Mac]

I've tried Aladin formula on the example No.'s you've given and 4 is the result????

 

[Aladin Akyurek]

On 2002-04-04 15:23, Eddie G. wrote:
Barrie, You are right, it works in that case but it doesn't work in the following case, the answer i want is 4 but aladyn's formula results in 3........

col a col b
1 1769
2 1748 1784
3 1755
4 1777

In that case, use e.g.,

=SUMPRODUCT((1*(ISNUMBER(A1:A4)+ISNUMBER(B1:B4))))-SUMPRODUCT(ISNUMBER(A1:A4)*ISNUMBER(B1:B4))

Aladin

 

[Ian Mac]

On 2002-04-04 15:38, Aladin Akyurek wrote:

On 2002-04-04 15:23, Eddie G. wrote:
Barrie, You are right, it works in that case but it doesn't work in the following case, the answer i want is 4 but aladyn's formula results in 3........

col a col b
1 1769
2 1748 1784
3 1755
4 1777

In that case, use e.g.,

=SUMPRODUCT((1*(ISNUMBER(A1:A4)+ISNUMBER(B1:B4))))-SUMPRODUCT(ISNUMBER(A1:A4)*ISNUMBER(B1:B4))

Aladin

Don't be smart!! your original formula has worked bothed time for me inc. the second. Why isn't it working for Eddie, is that not the question?

 

[Aladin Akyurek]

On 2002-04-04 15:44, Ian Mac wrote:

On 2002-04-04 15:38, Aladin Akyurek wrote:

On 2002-04-04 15:23, Eddie G. wrote:
Barrie, You are right, it works in that case but it doesn't work in the following case, the answer i want is 4 but aladyn's formula results in 3........

col a col b
1 1769
2 1748 1784
3 1755
4 1777

In that case, use e.g.,

=SUMPRODUCT((1*(ISNUMBER(A1:A4)+ISNUMBER(B1:B4))))-SUMPRODUCT(ISNUMBER(A1:A4)*ISNUMBER(B1:B4))

Aladin

Don't be smart!! your original formula has worked bothed time for me inc. the second. Why isn't it working for Eddie, is that not the question?

Ian,

Eddie's specs are much clearer now. For what follows he wants a count of 4 as result.

{"",1769;
1748,1784;
1755,"";
1777,""}

The MAX formula will produce a count of 3 here, as Eddie observes. His question boils down to, How many 2-cell rows are there in the target range that contain at least a number (or date)?

So, the new formula appeared to me the right thing to do.

Aladin

 

[Ian Mac]

On 2002-04-04 16:11, Aladin Akyurek wrote:

On 2002-04-04 15:44, Ian Mac wrote:

On 2002-04-04 15:38, Aladin Akyurek wrote:

On 2002-04-04 15:23, Eddie G. wrote:
Barrie, You are right, it works in that case but it doesn't work in the following case, the answer i want is 4 but aladyn's formula results in 3........

col a col b
1 1769
2 1748 1784
3 1755
4 1777

In that case, use e.g.,

=SUMPRODUCT((1*(ISNUMBER(A1:A4)+ISNUMBER(B1:B4))))-SUMPRODUCT(ISNUMBER(A1:A4)*ISNUMBER(B1:B4))

Aladin

Don't be smart!! your original formula has worked bothed time for me inc. the second. Why isn't it working for Eddie, is that not the question?

Ian,

Eddie's specs are much clearer now. For what follows he wants a count of 4 as result.

{"",1769;
1748,1784;
1755,"";
1777,""}

The MAX formula will produce a count of 3 here, as Eddie observes. His question boils down to, How many 2-cell rows are there in the target range that contain at least a number (or date)?

So, the new formula appeared to me the right thing to do.

Aladin

MUST apologise, when I saw the e.g. I was JUST looking at the forum view.

It's just this minute that I've realised using the [q]quote[/q] thingy you get the tabs also!!!!!!!.

I saw:

col a col b
1 1769
2 1748 1784
3 1755
4 1777

and you can quote me

I didn't see the "" in the:

{"",1769;
1748,1784;
1755,"";
1777,""}

sorry about that.