Find last cell with data - as formular

H

Hexagon

New Member
Joined
May 3, 2006
Messages
25
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:

Code: 
=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 :help:
 

Excel Facts

When did Power Query debut in Excel?
Although it was an add-in in Excel 2010 & Excel 2013, Power Query became a part of Excel in 2016, in Data, Get & Transform Data.
Sort by date Sort by votes
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="-"))
 
Upvote 0
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.
 
Upvote 0
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.
Click to expand...

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.
 
Upvote 0
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.
 
Upvote 0
Fab! FAb! Fab!

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

You're welcome.

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

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.
 
Upvote 0
You must log in or register to reply here.

Similar threads

R
  • Question Question
Highlighting cells based on criteria
Replies
0
Views
418
rahildhody
R
MrDB4Excel
  • Question Question
If Find Blank Cell
Replies
0
Views
696
MrDB4Excel
MrDB4Excel
E
  • Solved
Returning specific texts within a wide range of data coming from the Web
Replies
8
Views
288
Peter_SSs
Peter_SSs
M
  • Question Question
Find text in one cell, select all cells for same person on same date and copy to another sheet
Replies
0
Views
493
Moonriser
M
S
  • Solved
Seperate Data In One Cell Across Multiple Cells
Replies
6
Views
2K
Alex Blakenburg
A

Forum statistics

Threads
1,223,911
Messages
6,175,324
Members
452,635
Latest member
laura12345

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top