Saturday, April 26, 2014

yogi_Compute Rider Payouts By Name And Date Or Range Of Dates Based On Data In 'Fees List'


                                         Google Spreadsheet   Post  #1618
Yogi Anand, D.Eng, P.E.      ANAND Enterprises LLC -- Rochester Hills MI     www.energyefficientbuild.com.   Apr-26-2014
post by Margot Con: (https://productforums.google.com/forum/#!category-topic/docs/spreadsheets/dIJM6a-TF5E)
Trouble with Arrayformula and sumfilter or sumproduct
Hi,

I'm having trouble finding a sum of data filtered for two different conditions (date, and name)


The "Fees List" sheet contains the bulk of the data that I'm looking at. The "Rider Payouts" is where I'm trying to use the functions. In column B is a SUMIF function that gets the total $ per name for the whole Fees List sheet. I want to also be able to find totals by date, or even/especially by date range. 

I tried both of the following formulas, in columns F and G, to see which I could get to work:
=ArrayFormula(sumproduct(('Fees List'!B$5:B=A3)*('Fees List'!A$5:A=H$1)*'Fees List'!G$5:G))

=ArrayFormula(SUM(IF('Fees List'!B$5:B=A3,IF('Fees List'!A$5:A=G$1, 'Fees List'!G$5:G,0),0)))

Both formulas result in 0 for all rows.

I put a date in G1 as a reference point to try and filter by that date. I'd actually ideally like to be able to use a range of dates, but I wasn't sure how to do that, and wanted to get the rest of the function working before I played around with that.

Any thoughts on what I'm doing wrong? A syntax problem or reference error??

thanks!
---------------------------------------------------------------------------------------------------------------------------------------------------