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?

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?

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?

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?

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?

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?

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?

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?

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?

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

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?

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)