Tuesday, April 23, 2013

yogi_Using SUMPRODUCT Function In Google Spreadsheet For MultiConditional Sum


                                          Google Spreadsheet   Post  #1149
Yogi Anand, D.Eng, P.E.      ANAND Enterprises LLC -- Rochester Hills MI     www.energyefficientbuild.com.    Apr 24, 2013
user Lewis TW Koh :(http://productforums.google.com/forum/?zx=crpyw0e3sdva#!category-topic/docs/spreadsheets/STG-8g-mJjw)
How do I convert this formula from Excel to Google Doc and make it work?
Hi,

I have 2 formulas that works in excel but when I transfer to google doc, it gives me "#VALUE!". 
Does anyone has a guide on how to convert them to google doc?

=SUMPRODUCT(((E4:E73<>"")*(I4:I73<>"nil"))/COUNTIF(E4:E73,E4:E73&""))
The above suppose to do the following:
Apple1
Apple2
Apple1
Apple3 - Nil
Orange1
Orange1
Pear2
Pear1

Total fruits = 5 (exclude Apple 3 because it is marked as nil in column I) Repeated items will count as 1.

=(SUMPRODUCT((K4:K73<>"")/COUNTIF(K4:K73,K4:K73&"")))
The above suppose to do the following:
Apple1
Apple2
Apple1
Apple3
Orange1
Orange1
Pear2
Pear1
Total fruits = 6 (Repeated items will count as 1)
---
Sorry didn't read properly.


Regards,
Lewis
------------------------------------------------------------------------------------------------------------------
following is a solution to the problem