# Array formula returns 0

1. ## Array formula returns 0

I can't get this array =SUM(IF(Test_Query!\$A\$2:\$A\$726="1",IF(Test_Query!\$M\$2:\$M\$726="97",IF(Test_Query!\$H\$2:\$H\$726="EACH",Test_Query!\$G\$2:\$G\$726)))) to return the correct value. What I was hoping the formula would do is look at Column A for a criteria "1", then look in Column M for criteria "97", then look in Column H for criteria "EACH", and then sum all rows in Column G that meet all 3 criteria.
Also, the formula is on the same page as the data and the data that is being analyzed in has subtotals (Using the subtotal function).
Thanks for any help on this.  Register To Reply

2. =SUM(((Test_Query!\$A\$2:\$A\$726="1")*(Test_Query!\$ M\$2:\$M\$726="97")*(Test_Query!\$H\$2:\$H\$726="EACH"))*Test_Query!\$G\$2:\$G\$726)

Press Ctrl+Shift+Enter  Register To Reply

3. The values of 1 and 97 are they text or numbers? If you want numbers you do not want the quotation marks

=SUM(IF(Test_Query!\$A\$2:\$A\$726=1,IF(Test_Query!\$ M\$2:\$M\$726=97,IF(Test_Query!\$H\$2:\$H\$726="EACH",T est_Query!\$G\$2:\$G\$726))))

Regards

Dav  Register To Reply