All about numbers
 

I have the following table:

A B C D
3 1 0.5 0
2 2 0 0.5
3 2 0 0
0 1 0.5 0
1 0 0 0
2 1 0 1
1 2 0.5 0
4 1 0 1.5

The formula required is to:
1) check if any row in column C has 0 (zero) and see corresponding cell in
column D also has 0 (zero). Table above shows row 3 and 5.
2) if matches, then check if (cell A - cell B) of the same row where the
above condition is true returns a positive value.
3) if a positive value is returned, then count it. Above example will return
a value of 2 (both occurences of 0 in C3 & C5).

Can anyone help?

All about numbers
 

Try this:

=SUMPRODUCT(--(A1:A8-B1:B80),--(C1:C8=0),--(D1:D8=0))

--
Biff
Microsoft Excel MVP


"andrew" wrote in message
...
I have the following table:

A B C D
3 1 0.5 0
2 2 0 0.5
3 2 0 0
0 1 0.5 0
1 0 0 0
2 1 0 1
1 2 0.5 0
4 1 0 1.5

The formula required is to:
1) check if any row in column C has 0 (zero) and see corresponding cell in
column D also has 0 (zero). Table above shows row 3 and 5.
2) if matches, then check if (cell A - cell B) of the same row where the
above condition is true returns a positive value.
3) if a positive value is returned, then count it. Above example will
return
a value of 2 (both occurences of 0 in C3 & C5).

Can anyone help?

All about numbers
 

One way
Assume data as posted within A1:D8
Placed in say, F1:
=SUMPRODUCT((C1:C8=0)*(D1:D8=0)*(C1:C8<"")*(D1:D8 <"")*(A1:A8-B1:B80))

The "additional" conditions: .. *(C1:C8<"")*(D1:D8<"")*
are precautions taken since blank cells are evaluated as zeros
--
Max
Singapore
http://savefile.com/projects/236895
Downloads: 15,500, Files: 352, Subscribers: 53
xdemechanik
---
"andrew" wrote:
I have the following table:

A B C D
3 1 0.5 0
2 2 0 0.5
3 2 0 0
0 1 0.5 0
1 0 0 0
2 1 0 1
1 2 0.5 0
4 1 0 1.5

The formula required is to:
1) check if any row in column C has 0 (zero) and see corresponding cell in
column D also has 0 (zero). Table above shows row 3 and 5.
2) if matches, then check if (cell A - cell B) of the same row where the
above condition is true returns a positive value.
3) if a positive value is returned, then count it. Above example will return
a value of 2 (both occurences of 0 in C3 & C5).

Can anyone help?

All about numbers
 

I used this earlier but didn't work:

=SUMPRODUCT((C2:C9=0)*(D2:D9=0)*((A2:A9)(B2:B9)))

When i checked, some rows in my actual data were empty. Will try again.

"T. Valko" wrote:

Try this:

=SUMPRODUCT(--(A1:A8-B1:B80),--(C1:C8=0),--(D1:D8=0))

--
Biff
Microsoft Excel MVP


"andrew" wrote in message
...
I have the following table:

A B C D
3 1 0.5 0
2 2 0 0.5
3 2 0 0
0 1 0.5 0
1 0 0 0
2 1 0 1
1 2 0.5 0
4 1 0 1.5

The formula required is to:
1) check if any row in column C has 0 (zero) and see corresponding cell in
column D also has 0 (zero). Table above shows row 3 and 5.
2) if matches, then check if (cell A - cell B) of the same row where the
above condition is true returns a positive value.
3) if a positive value is returned, then count it. Above example will
return
a value of 2 (both occurences of 0 in C3 & C5).

Can anyone help?

All about numbers
 

Hi Max,
It worked on my test file BUT on the actual file (which extends the rows to
800), it doesn't seem to be working.

I realised that the cells in reference has formulas within them (all four
columns per below). Eg Column A =IF(ISBLANK(Fix!A7),"",Fix!A7)

Is this a problem?


"Max" wrote:

One way
Assume data as posted within A1:D8
Placed in say, F1:
=SUMPRODUCT((C1:C8=0)*(D1:D8=0)*(C1:C8<"")*(D1:D8 <"")*(A1:A8-B1:B80))

The "additional" conditions: .. *(C1:C8<"")*(D1:D8<"")*
are precautions taken since blank cells are evaluated as zeros
--
Max
Singapore
http://savefile.com/projects/236895
Downloads: 15,500, Files: 352, Subscribers: 53
xdemechanik
---
"andrew" wrote:
I have the following table:

A B C D
3 1 0.5 0
2 2 0 0.5
3 2 0 0
0 1 0.5 0
1 0 0 0
2 1 0 1
1 2 0.5 0
4 1 0 1.5

The formula required is to:
1) check if any row in column C has 0 (zero) and see corresponding cell in
column D also has 0 (zero). Table above shows row 3 and 5.
2) if matches, then check if (cell A - cell B) of the same row where the
above condition is true returns a positive value.
3) if a positive value is returned, then count it. Above example will return
a value of 2 (both occurences of 0 in C3 & C5).

Can anyone help?

All about numbers
 

some rows in my actual data were empty.

Which columns can be empty? Any of them?

This assumes every column *must* have an entry *and* the values in columns
C&D will always be positive:

=SUMPRODUCT(--(MMULT(--(ISNUMBER(A1:D8)),{1;1;1;1})=4),--(A1:A8-B1:B80),--(C1:C8+D1:D8=0))


--
Biff
Microsoft Excel MVP


"andrew" wrote in message
...
I used this earlier but didn't work:

=SUMPRODUCT((C2:C9=0)*(D2:D9=0)*((A2:A9)(B2:B9)))

When i checked, some rows in my actual data were empty. Will try again.

"T. Valko" wrote:

Try this:

=SUMPRODUCT(--(A1:A8-B1:B80),--(C1:C8=0),--(D1:D8=0))

--
Biff
Microsoft Excel MVP


"andrew" wrote in message
...
I have the following table:

A B C D
3 1 0.5 0
2 2 0 0.5
3 2 0 0
0 1 0.5 0
1 0 0 0
2 1 0 1
1 2 0.5 0
4 1 0 1.5

The formula required is to:
1) check if any row in column C has 0 (zero) and see corresponding cell
in
column D also has 0 (zero). Table above shows row 3 and 5.
2) if matches, then check if (cell A - cell B) of the same row where
the
above condition is true returns a positive value.
3) if a positive value is returned, then count it. Above example will
return
a value of 2 (both occurences of 0 in C3 & C5).

Can anyone help?

All about numbers
 

I realised that the cells in reference has formulas within them (all four
columns per below). Eg Column A =IF(ISBLANK(Fix!A7),"",Fix!A7)
Is this a problem?

So, any cell in a row may contain a formula blank? If that's the case
disregard the suggestions I've already made.

=SUMPRODUCT(--(MMULT(--(ISNUMBER(A1:D8)),{1;1;1;1})=4),--(A1:A8B1:B8),--(C1:C8=0),--(D1:D8=0))


--
Biff
Microsoft Excel MVP


"T. Valko" wrote in message
...
some rows in my actual data were empty.

Which columns can be empty? Any of them?

This assumes every column *must* have an entry *and* the values in columns
C&D will always be positive:

=SUMPRODUCT(--(MMULT(--(ISNUMBER(A1:D8)),{1;1;1;1})=4),--(A1:A8-B1:B80),--(C1:C8+D1:D8=0))


--
Biff
Microsoft Excel MVP


"andrew" wrote in message
...
I used this earlier but didn't work:

=SUMPRODUCT((C2:C9=0)*(D2:D9=0)*((A2:A9)(B2:B9)))

When i checked, some rows in my actual data were empty. Will try again.

"T. Valko" wrote:

Try this:

=SUMPRODUCT(--(A1:A8-B1:B80),--(C1:C8=0),--(D1:D8=0))

--
Biff
Microsoft Excel MVP


"andrew" wrote in message
...
I have the following table:

A B C D
3 1 0.5 0
2 2 0 0.5
3 2 0 0
0 1 0.5 0
1 0 0 0
2 1 0 1
1 2 0.5 0
4 1 0 1.5

The formula required is to:
1) check if any row in column C has 0 (zero) and see corresponding
cell in
column D also has 0 (zero). Table above shows row 3 and 5.
2) if matches, then check if (cell A - cell B) of the same row where
the
above condition is true returns a positive value.
3) if a positive value is returned, then count it. Above example will
return
a value of 2 (both occurences of 0 in C3 & C5).

Can anyone help?

All about numbers
 

Eg Column A =IF(ISBLANK(Fix!A7),"",Fix!A7)
Is this a problem?

Yes, it is. Suggest you change it all to return a zero instead of "" (which
is text), viz.:
=IF(ISBLANK(Fix!A7),0,Fix!A7)

Then you could use the simple:
=SUMPRODUCT((C1:C800=0)*(D1:D800=0)*(A1:A800-B1:B8000))
to get the result
--
Max
Singapore
http://savefile.com/projects/236895
Downloads: 15,500, Files: 352, Subscribers: 53
xdemechanik
---
"andrew" wrote:
Hi Max,
It worked on my test file BUT on the actual file (which extends the rows to
800), it doesn't seem to be working.

I realised that the cells in reference has formulas within them (all four
columns per below). Eg Column A =IF(ISBLANK(Fix!A7),"",Fix!A7)

Is this a problem?

All about numbers
 

Ah, instead of:
=IF(ISBLANK(Fix!A7),0,Fix!A7)

Just use a simple link will do: =Fix!A7
(Any blank source cells will be returned as zeros)
--
Max
Singapore
http://savefile.com/projects/236895
Downloads: 15,500, Files: 352, Subscribers: 53
xdemechanik
---

All about numbers
 

Ok, got it. Base on the same table, what if i want to check the following:

1) check if any row in column C has 0.5 while column D has 0. Table above
shows row 1,4 and 7.
2) if matches, then check if (cell A - cell B) +0.5 0
3) if a positive value is returned, then count it. Above example will return
a value of 1 (row 1 returns positive while row 4 and 7 returns 0)




"Max" wrote:

Eg Column A =IF(ISBLANK(Fix!A7),"",Fix!A7)
Is this a problem?

Yes, it is. Suggest you change it all to return a zero instead of "" (which
is text), viz.:
=IF(ISBLANK(Fix!A7),0,Fix!A7)

Then you could use the simple:
=SUMPRODUCT((C1:C800=0)*(D1:D800=0)*(A1:A800-B1:B8000))
to get the result
--
Max
Singapore
http://savefile.com/projects/236895
Downloads: 15,500, Files: 352, Subscribers: 53
xdemechanik
---
"andrew" wrote:
Hi Max,
It worked on my test file BUT on the actual file (which extends the rows to
800), it doesn't seem to be working.

I realised that the cells in reference has formulas within them (all four
columns per below). Eg Column A =IF(ISBLANK(Fix!A7),"",Fix!A7)

Is this a problem?

All about numbers
 

Thanks Biff. Looks good, but i'm having trouble modifying it suit the various
conditions i have in my table. (See earlier post to Max)

The pairings for column C and D include:
0.5 and 0
1 and 0
etc.

for each of the above that matches, the formula will then check if column
A-column B of the row is more than column C of the same row. E.g. using the
same table, row 1,4 and 7 has column C and D with 0.5 and 0 as the match. For
each match, the formula will then take column A-column B + 0.5 to return a
positive value. If positive value is return, it counts it, else 0.

"T. Valko" wrote:

I realised that the cells in reference has formulas within them (all four
columns per below). Eg Column A =IF(ISBLANK(Fix!A7),"",Fix!A7)
Is this a problem?

So, any cell in a row may contain a formula blank? If that's the case
disregard the suggestions I've already made.

=SUMPRODUCT(--(MMULT(--(ISNUMBER(A1:D8)),{1;1;1;1})=4),--(A1:A8B1:B8),--(C1:C8=0),--(D1:D8=0))


--
Biff
Microsoft Excel MVP


"T. Valko" wrote in message
...
some rows in my actual data were empty.

Which columns can be empty? Any of them?

This assumes every column *must* have an entry *and* the values in columns
C&D will always be positive:

=SUMPRODUCT(--(MMULT(--(ISNUMBER(A1:D8)),{1;1;1;1})=4),--(A1:A8-B1:B80),--(C1:C8+D1:D8=0))


--
Biff
Microsoft Excel MVP


"andrew" wrote in message
...
I used this earlier but didn't work:

=SUMPRODUCT((C2:C9=0)*(D2:D9=0)*((A2:A9)(B2:B9)))

When i checked, some rows in my actual data were empty. Will try again.

"T. Valko" wrote:

Try this:

=SUMPRODUCT(--(A1:A8-B1:B80),--(C1:C8=0),--(D1:D8=0))

--
Biff
Microsoft Excel MVP


"andrew" wrote in message
...
I have the following table:

A B C D
3 1 0.5 0
2 2 0 0.5
3 2 0 0
0 1 0.5 0
1 0 0 0
2 1 0 1
1 2 0.5 0
4 1 0 1.5

The formula required is to:
1) check if any row in column C has 0 (zero) and see corresponding
cell in
column D also has 0 (zero). Table above shows row 3 and 5.
2) if matches, then check if (cell A - cell B) of the same row where
the
above condition is true returns a positive value.
3) if a positive value is returned, then count it. Above example will
return
a value of 2 (both occurences of 0 in C3 & C5).

Can anyone help?

All about numbers
 

Sorry, now I'm lost on what you're trying to do. This sounds like the same
thing you posted a few weeks ago. I was lost on that as well.

--
Biff
Microsoft Excel MVP


"andrew" wrote in message
...
Thanks Biff. Looks good, but i'm having trouble modifying it suit the
various
conditions i have in my table. (See earlier post to Max)

The pairings for column C and D include:
0.5 and 0
1 and 0
etc.

for each of the above that matches, the formula will then check if column
A-column B of the row is more than column C of the same row. E.g. using
the
same table, row 1,4 and 7 has column C and D with 0.5 and 0 as the match.
For
each match, the formula will then take column A-column B + 0.5 to return a
positive value. If positive value is return, it counts it, else 0.

"T. Valko" wrote:

I realised that the cells in reference has formulas within them (all
four
columns per below). Eg Column A =IF(ISBLANK(Fix!A7),"",Fix!A7)
Is this a problem?

So, any cell in a row may contain a formula blank? If that's the case
disregard the suggestions I've already made.

=SUMPRODUCT(--(MMULT(--(ISNUMBER(A1:D8)),{1;1;1;1})=4),--(A1:A8B1:B8),--(C1:C8=0),--(D1:D8=0))


--
Biff
Microsoft Excel MVP


"T. Valko" wrote in message
...
some rows in my actual data were empty.

Which columns can be empty? Any of them?

This assumes every column *must* have an entry *and* the values in
columns
C&D will always be positive:

=SUMPRODUCT(--(MMULT(--(ISNUMBER(A1:D8)),{1;1;1;1})=4),--(A1:A8-B1:B80),--(C1:C8+D1:D8=0))


--
Biff
Microsoft Excel MVP


"andrew" wrote in message
...
I used this earlier but didn't work:

=SUMPRODUCT((C2:C9=0)*(D2:D9=0)*((A2:A9)(B2:B9)))

When i checked, some rows in my actual data were empty. Will try
again.

"T. Valko" wrote:

Try this:

=SUMPRODUCT(--(A1:A8-B1:B80),--(C1:C8=0),--(D1:D8=0))

--
Biff
Microsoft Excel MVP


"andrew" wrote in message
...
I have the following table:

A B C D
3 1 0.5 0
2 2 0 0.5
3 2 0 0
0 1 0.5 0
1 0 0 0
2 1 0 1
1 2 0.5 0
4 1 0 1.5

The formula required is to:
1) check if any row in column C has 0 (zero) and see corresponding
cell in
column D also has 0 (zero). Table above shows row 3 and 5.
2) if matches, then check if (cell A - cell B) of the same row
where
the
above condition is true returns a positive value.
3) if a positive value is returned, then count it. Above example
will
return
a value of 2 (both occurences of 0 in C3 & C5).

Can anyone help?

All about numbers
 

Ok, got it.

Good. Pl press the "Yes" button (like the one below) in that response, won't
you.

As for your new query, this should do it:
=SUMPRODUCT((C1:C8=0.5)*(D1:D8=0)*(A1:A8-B1:B8+0.50))

Above expression is simply strung up by following your logic lines
described. And if that does the job for your new query here, press the "Yes"
button below.
--
Max
Singapore
http://savefile.com/projects/236895
Downloads: 15,500, Files: 352, Subscribers: 53
xdemechanik
---
"andrew" wrote:
Ok, got it. Base on the same table, what if i want to check the following:

1) check if any row in column C has 0.5 while column D has 0. Table above
shows row 1,4 and 7.
2) if matches, then check if (cell A - cell B) +0.5 0
3) if a positive value is returned, then count it. Above example will return
a value of 1 (row 1 returns positive while row 4 and 7 returns 0)

All about numbers
 

No worries. Thanks to you guys, i managed to play around and found what i
wanted. I followed one of your advice to compile into 1 worksheet so that it
is able to sort and retrieve data accordingly - hence the reason i need to
modify the previous formula which was in 20 worksheets.


"T. Valko" wrote:

Sorry, now I'm lost on what you're trying to do. This sounds like the same
thing you posted a few weeks ago. I was lost on that as well.

--
Biff
Microsoft Excel MVP


"andrew" wrote in message
...
Thanks Biff. Looks good, but i'm having trouble modifying it suit the
various
conditions i have in my table. (See earlier post to Max)

The pairings for column C and D include:
0.5 and 0
1 and 0
etc.

for each of the above that matches, the formula will then check if column
A-column B of the row is more than column C of the same row. E.g. using
the
same table, row 1,4 and 7 has column C and D with 0.5 and 0 as the match.
For
each match, the formula will then take column A-column B + 0.5 to return a
positive value. If positive value is return, it counts it, else 0.

"T. Valko" wrote:

I realised that the cells in reference has formulas within them (all
four
columns per below). Eg Column A =IF(ISBLANK(Fix!A7),"",Fix!A7)
Is this a problem?

So, any cell in a row may contain a formula blank? If that's the case
disregard the suggestions I've already made.

=SUMPRODUCT(--(MMULT(--(ISNUMBER(A1:D8)),{1;1;1;1})=4),--(A1:A8B1:B8),--(C1:C8=0),--(D1:D8=0))


--
Biff
Microsoft Excel MVP


"T. Valko" wrote in message
...
some rows in my actual data were empty.

Which columns can be empty? Any of them?

This assumes every column *must* have an entry *and* the values in
columns
C&D will always be positive:

=SUMPRODUCT(--(MMULT(--(ISNUMBER(A1:D8)),{1;1;1;1})=4),--(A1:A8-B1:B80),--(C1:C8+D1:D8=0))


--
Biff
Microsoft Excel MVP


"andrew" wrote in message
...
I used this earlier but didn't work:

=SUMPRODUCT((C2:C9=0)*(D2:D9=0)*((A2:A9)(B2:B9)))

When i checked, some rows in my actual data were empty. Will try
again.

"T. Valko" wrote:

Try this:

=SUMPRODUCT(--(A1:A8-B1:B80),--(C1:C8=0),--(D1:D8=0))

--
Biff
Microsoft Excel MVP


"andrew" wrote in message
...
I have the following table:

A B C D
3 1 0.5 0
2 2 0 0.5
3 2 0 0
0 1 0.5 0
1 0 0 0
2 1 0 1
1 2 0.5 0
4 1 0 1.5

The formula required is to:
1) check if any row in column C has 0 (zero) and see corresponding
cell in
column D also has 0 (zero). Table above shows row 3 and 5.
2) if matches, then check if (cell A - cell B) of the same row
where
the
above condition is true returns a positive value.
3) if a positive value is returned, then count it. Above example
will
return
a value of 2 (both occurences of 0 in C3 & C5).

Can anyone help?