need help with counting criteria without counting duplicate rows

Hello, I'm Lya. I've been trying to figure this out and I can't quite seem to figure it out. I have a list of data for a Log in sheet1 as follows:
A "name"
B "state"
C "status"
D "agent"

I would like to summarize the list in sheet2, which contains 2 tables.
Table 1 at the top is counting the different "status" (quote, pending, issued, denied) for specific "state". I have tried using, for example {=COUNT(IF(Sheet1!B:B="al", IF(Sheet1!C:C="pending",)))} and {=SUM((Sheet1!B:B="al")*(Sheet1!C:C="quote"))} both work except not the exact data I wanted.

I have found the # of unique "names" with {=SUM(IF(FREQUENCY(IF(LEN(A2:A15)>0,MATCH(A2:A15,A2:A15,0),""), IF(LEN(A2:A15)>0,MATCH(A2:A15,A2:A15,0),""))>0,1))}

I'm trying to explain and word this right so hopefully you can understand what I'm trying to say. Column A "names" can have multiple entries for different status, for example "John Doe" wants to have a quote done, but he did not give all the correct information, his name gets entered into the Log sheet, but his status is either "pending" or "denied". There may be many entries (rows) for him with a "pending" status. He later comes back with the correct information, his name is again entered into the Log sheet (in another row), he receives his quote and status is "quote". Now if he issues the quote it is NOT entered in a new row, the "quote" would be used and changed to "issue".

So for the summary table 1, I want it to take the last entry of customer "name" and determine if the "status" and "state".

For table 2, I'm calculating data per agent, and similar to the first table, I want to calculate the number of each "name" and "status", but I only want the final entry for each customer "name" to be counted. I love coding, but when your staring at the screen for hours and hours, all the formulas start blurring. Sorry if this is confusing. Can anyone please help me? This project is using Excel 2003.Thanks for reading!

Hi Lancerwr, thanks for help with table 2! I had played around with doing the countif function to get the index, but I just didn't know how to put it in the formulas. I wanted to avoid using a helper column, but as long as I get the correct result it works, Thanks!!! any chance I could use the same formula to get the results for table 1? Would using the Match or Lookup function work?