Saturday, March 15, 2014

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   Mar-15-2014
post by Drew hammer (!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.

Thanks for your help!