Tuesday, February 26, 2013

yogi_Perform Row by Row Count Based On Multiple Conditions In Another Sheet

                                          Google Spreadsheet   Post  #1049
Yogi Anand, D.Eng, P.E.      ANAND Enterprises LLC -- Rochester Hills MI     www.energyefficientbuild.com.    Feb 26, 2013
user the malaise said:(http://productforums.google.com/forum/?zx=d9dujze9rels#!category-topic/docs/spreadsheets/5Z5vgnAO1ng)
How do I write spreadsheet function that references multiple variables for email tracking?

I am trying to learn how to create functions in a Google Spreadsheet that I will be using to track success rate of template emails. Here is the example: https://docs.google.com/spreadsheet/ccc?key=0AppkK6kryA8qdGNuTmtjdXZiUklBZEg1N3JWMWpmcmc&usp=sharing
What would the functions be for 'Template Tracking' page in C2, D2 and E2?
Essentially if a value exists in 'Email Tracking' column D then count it (if nothing there, then don't) and the create sum in appropriate place on 'Template Tracking' page based on 'Template #' in 'Email Tracking' column C.
For basic example, I used the function
=countIf('Email Tracking'!C:C;"Template #1")
for Column B of 'Template Tracking' page

...it seems to work great for what I am looking for, though cannot seem to process 'zero' quantity (puts 1 instead). Any ideas of a workaround for that?

Greatly appreciate your help.

following is a solution to the problem