All about numbers

[andrew]

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?

 

[T. Valko]

Try this:

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

 

[Max]

One way
Assume data as posted within A18
Placed in say, F1:
=SUMPRODUCT((C1:C8=0)*(D18=0)*(C1:C8<>"")*(D18<>"")*(A1:A8-B1:B8>0))

The "additional" conditions: .. *(C1:C8<>"")*(D18<>"")*
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]

I used this earlier but didn't work:

=SUMPRODUCT((C2:C9=0)*(D29=0)*((A2:A9)>(B2:B9)))

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

 

[andrew]

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?

 

[T. Valko]

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(A18)),{1;1;1;1})=4),--(A1:A8-B1:B8>0),--(C1:C8+D18=0))

 

[T. Valko]

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(A18)),{1;1;1;1})=4),--(A1:A8>B1:B8),--(C1:C8=0),--(D18=0))

 

[Max]

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)*(D1800=0)*(A1:A800-B1:B800>0))
to get the result
--
Max
Singapore
http://savefile.com/projects/236895
Downloads: 15,500, Files: 352, Subscribers: 53
xdemechanik
---

 

[Max]

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

 

[andrew]

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)

 

[andrew]

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]

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.

 

[Max]

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)*(D18=0)*(A1:A8-B1:B8+0.5>0))

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]

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.