Thread: Arrayformulas with either/or nested to sumproduct if eitherconditions are true
View Single Post
  #13   Report Post  
Posted to microsoft.public.excel.worksheet.functions
Claus Busch Claus Busch is offline
external usenet poster
  
Posts: 3,872
Default Arrayformulas with either/or nested to sumproduct if either conditions are true
Hi Jonathan,

Am Fri, 28 Nov 2014 08:11:55 -0800 (PST) schrieb Jonathan Pence:

everything works except the "combined" formula:

=SUMPRODUCT(($A$18:$A$35=$F$142)*($B$18:$B$35<"-")*$M$18:$M$35*N$18:N$35)/SUMPRODUCT(($A$18:$A$35=$F$142)*($B$18:$B$35<"-")*$M$18:$M$35)

check the character in column B. I saw the hyphen. Evenutally there are
also one or more leading or trailing spaces in the cell. Then change the
hyphen with the cell content. Or run Data = TextToColumns for column B
to delete the spaces. The formula is correct.


Regards
Claus B.
--
Vista Ultimate / Windows7
Office 2007 Ultimate / 2010 Professional
Reply With QuoteReply With Quote