Monday, August 6, 2012

yogi_Compute Number Of Interactions And Total Time Of Interactions In Minutes By Date

Yogi Anand, D.Eng, P.E.       Google Spreadsheet    Post  #676   Aug 06, 2012

use |meNToS| said: (!category-topic/docs/spreadsheets/2ZaBNBrGziw)
Need help with multiple criteria for countif and sumif
Hi everybody!
Need help of doing the following - 
1. I have spreadsheet with two pages. One has data I need to calculate and get results in second page. Data sheet looks like this - 
Timestamp Type of interaction Time of interaction (min)  
8/3/2012      Chat                    10
8/4/2012      Chat                     13
8/2/2012      Phone Call               25
8/5/2012      Phone Call               2
8/5/2012      Chat                     12
8/6/2012      Phone Call               4
8/6/2012      Phone Call               4
8/6/2012      Phone Call               7
8/6/2012      Phone Call                4
8/6/2012      Phone Call               9
8/6/2012       Phone Call             15
8/6/2012      Phone Call               8
This Data sheet is filled up by a form, so date is something that generates automatically. Now, I need this information on second sheet to be calculated and it should look like this - 

         Amount of Call  Total Time on the Phone Amount of Chats Total Time in Chat

So I want to use CountIF with two criterias - Count number of interactions for Chat on this date (same for the phone)
               to use SumIF with two criterias - Cum of minutes of Chats for this date (same for the phone)
I tried about ten different formulas for each situation and all I get is 0 or N/A. Please help with this. Thank you in advance
following is a solution to the problem