Google Spreadsheet Post #834
Yogi Anand, D.Eng, P.E. ANAND Enterprises LLC -- Rochester Hills MI www.energyefficientbuild.com. Oct 27, 2012
user robjwill said: (http://productforums.google.com/forum/#!category-topic/docs/how-do-i/Iz-9Wx4vadM)
Countifs equivalent for Google Spreadsheet not working
I am trying to
implement a Countifs equivalent in a Google spreadsheet.
Problem 1:
Range 1 = Locations, Criteria1 = C2:C, Range 2 = Dates, Criteria2 = "".
So I am trying to count locations for each name in cols C2:C that have a blank date value in Dates range. (Note, the dates are imported from another spreadsheet using importrange, formatted as m/d/yyyy, - if that matters...)
I tried, arrayformula(sum(Locations=C2:
Problem 2:
Then, I need to do the exact same think, but for dates that are not blank.
Thanks!
Problem 1:
Range 1 = Locations, Criteria1 = C2:C, Range 2 = Dates, Criteria2 = "".
So I am trying to count locations for each name in cols C2:C that have a blank date value in Dates range. (Note, the dates are imported from another spreadsheet using importrange, formatted as m/d/yyyy, - if that matters...)
I tried, arrayformula(sum(Locations=C2:
Problem 2:
Then, I need to do the exact same think, but for dates that are not blank.
Thanks!
-------------------------------------------------------------------------------------------------
following is a solution to the problem
Thanks Yogi, however, I think in my effort to simplify my need, I don't think I sufficiently described the problem in accurately. Please see the url to the sheet.
ReplyDeletehttps://docs.google.com/a/levelgroup.com/spreadsheet/ccc?key=0Agcb8bUVVOOodEtDaWxya2JMQjRkXzdrcUhBamlUdHc#gid=1
The Active column should return the number of Neighborhoods (from the Locations range in the Location Analysis sheet) that have a blank Sold date (from the Sold range in the Location Analysis sheet). The Excel equivalent would be something like: Countifs(Locations,Neighborhoods,Sold,""). The Sold column is similar, Countifs(Locations,Neighborhoods,Sold,>0). If I filter the Location Analysis sheet (using the list view), the Active result for Bed-Stuy should be 12, and the Sold result should be the total 30 (from the All column) less the 12 active, which would be 18. The formula needed should return the Active and Sold values for each of the Neighborhoods.
Hi robjwill:
ReplyDeleteI will have a look at your spreadsheet and respond in Google Docs Help forum if I have something to add.
Cheers!
Yogi
Cloud Computing -- Google Docs Way
yogi--anand-consulting.blogspot.com