yogi_Compile From DataTable List Of Unique Clients DateRange MostRecentServiceDate NoOfMeetings And Hours

                                         Google Spreadsheet   Post  #1561
Yogi Anand, D.Eng, P.E.      ANAND Enterprises LLC -- Rochester Hills MI     www.energyefficientbuild.com.   Mar-15-2014
post by Drew hammer (https://productforums.google.com/forum/#!category-topic/docs/spreadsheets/oOBKPYz6clM)
How do I add the quantities in a specific column based on conditions in other columns?

Thank you in advance. These forums are amazing!

I'm trying to do some analysis on a report I've run from our time reporting software. I've tried many different formulas using these forums but I can't get it right.

Goal: To count the values in a specific column based on values (either text or date) in other columns.

The report I import is columns A through D.

The calculations are rows F through J.

For column J, I used Unique(B2:B) to list each client. Is there any to have it ignore blank cells?

For column G, I want to calculate the difference between the first date of service and the last date of service.

For column H, I want to list the most recent service date. For example, for "Smith: Joe" it would display 1/24/2014.

For column I, it counts the number of meetings. I used, "=ArrayFormula(if(len(F2:F),countif(B2:B,F2:F),iferror(1/0)))"

For column J, I want to total Hours for each client.

Here is the shared spreadsheet, https://docs.google.com/spreadsheet/ccc?key=0AsBHq5KY4yb_dEVzSFl5OFpWWW85WWpuZ2FBZnBnNVE&usp=sharing.

Thanks for your help!

---------------------------------------------------------------------------------------------------------------------------------------------------------------