Thanks:  0
Likes:  0

1. 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.p...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.

2. 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.p...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.p...4021&forum=2&5

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

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

5. 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.p...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

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

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

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

8. 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))

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?

9. 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))

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.

10. 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))

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.

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,""}

User Tag List

Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•