Prev: Conditional Format - Identifying Highlighted Cells
Next: Can not print in Color
From: andrew on 6 Jul 2008 22:00
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?


From: T. Valko on 6 Jul 2008 22:19
Try this:

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

--
Biff
Microsoft Excel MVP


"andrew" <andrew(a)discussions.microsoft.com> wrote in message
news:E7392058-2293-40E7-8317-52744015D09A(a)microsoft.com...
>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?
>
>


From: Max on 6 Jul 2008 22:20
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:B8>0))

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?
>
>
From: andrew on 6 Jul 2008 22:29
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:B8>0),--(C1:C8=0),--(D1:D8=0))
>
> --
> Biff
> Microsoft Excel MVP
>
>
> "andrew" <andrew(a)discussions.microsoft.com> wrote in message
> news:E7392058-2293-40E7-8317-52744015D09A(a)microsoft.com...
> >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?
> >
> >
>
>
>
From: andrew on 6 Jul 2008 22:38
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:B8>0))
>
> 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?
> >
> >
 |  Next  |  Last
Pages: 1 2 3
Prev: Conditional Format - Identifying Highlighted Cells
Next: Can not print in Color