Find last cell with data - as formular

[Hexagon]

Hope someone can help

I am trying to inorporate a formular thatwill find the last with data (not formular) in a speciifc column.

The formula is to be incorporated into an array, searching a database with two criterias. However, for the formular to return the correct Count value, it must know which is the last cell.

The scenario is this:

Find Zero value in column D if Condition 1 (Guarantee) and Condition 2 ( GBP Value = 0). The formular I am using is this:

=SUM(IF('DocCred-Gty Database'!$E$8:$E$173='ANalysis II'!B25,IF('DocCred-Gty Database'!$M$8:$M$173="-",1,0),1))

In the above formular I need to ensure that it only sums those cells that have a numer or text and not those with predefined formular in teh following cells.

Hope this makes sense for you Gurus to help a poor maiden out

 

[Aladin Akyurek]

Are you saying that the formula you posted yields a wrong count? If so, does it produce a different result from:

=SUMPRODUCT(--('DocCred-Gty Database'!$E$8:$E$173='ANalysis II'!B25),--('DocCred-Gty Database'!$M$8:$M$173="-"))

 

[Hexagon]

Thank you for your reply

YES my formular returns an incorrect result.

The formular you supplied returns all Zero's but there should be some numbers.

 

[Aladin Akyurek]

Thank you for your reply

YES my formular returns an incorrect result.

The formular you supplied returns all Zero's but there should be some numbers.

No clue what that means. If I visualize you data DocCred-Gty Database something like this:

E8: FRA
E9: FRA
E10: VDA
E11: FRA

M8: -
M9:
M10: -
M11: -

and if 'ANalysis II'!B25 houses FRA as criterion, the formula will yield a count of 2.

 

[Hexagon]

Fab! FAb! Fab!

Many thank you's, that was of great help.

If you have the time, I would greatly appreciate it if you could explain the function of " -- " in the formula.

Thank you.

 

[Aladin Akyurek]

Fab! FAb! Fab!

Many thank you's, that was of great help.

You're welcome.

If you have the time, I would greatly appreciate it if you could explain the function of " -- " in the formula.
...

SumProduct multiplies arrays/vectors elementwise and sums the results of multiplication: Given...

A1: 1
A2: 1
A3: 0
A4: 1

B1: 1
B2: 0
B3: 0
B4: 1

we get:

=SUMPRODUCT(A1:A4,B1:B4)

==> SUMPRODUCT({1;1;0;1},{1;0;0;1})

==> SUMPRODUCT({1;0;0;1})

==> 2

The foregoing shows how the function process vectors/arrays of numbers.

Now suppose that we have:

A1: FRA
A2: FRA
A3: VDA
A4: FRA

B1: -
B2:
B3: -
B4: -

And we want to count records in A:B where A is "FRA" and B is "-". This requires testing A1:A4 for being equal to "FRA" and B1:B4 to "-". We konow that:

=SUMPRODUCT((A1:A4="FRA"),(B1:B4="-"))

==> SUMPRODUCT({TRUE;TRUE;FALSE;TRUE},{TRUE;FALSE;TRUE;TRUE})

SumProduct alas cannot work with TRUE's and FALSE's, but we know that the Excel numeric equivalents for TRUE and FALSE are 1 and 0. We also know that:

--TRUE yields 1 and --FALSE yields 0. Given this equivalence, we then construct:

=SUMPRODUCT(--(A1:A4="FRA"),--(B1:B4="-"))

in order to get:

==> SUMPRODUCT({1;1;0;1},{1;0;0;1})

==> SUMPRODUCT({1;0;0;1})

==> 2

Thus, -- enables us to use SumProduct with conditional expressions. BTW, the following also would effect what we want:

=SUMPRODUCT((A1:A4="FRA")+0,(B1:B4="-")+0)

because TRUE+0 is 1 and FALSE+0 is 0.

 

[Hexagon]

Thank you very much for your help and the excellent explanation