# Add figures based on 3 criteria from Test Data Sheet

1. ## Add figures based on 3 criteria from Test Data Sheet

Hi need help with a formula to add figures based on 3 criteria where the 3rd criteria is (months) variable. I have added a test file. Thanks  Register To Reply

2. ## Re: Add figures based on 3 criteria from Test Data Sheet

Try:

=IF(A10="","",SUMPRODUCT('Test Data'!\$C\$9:\$Z\$26,('Test Data'!\$A\$9:\$A\$26=\$A10)*('Test Data'!\$B\$9:\$B\$26=B\$9)*ISNUMBER(MATCH('Test Data'!\$C\$7:\$Z\$7,{"June","July","August"},0))))

copied down and to next column  Register To Reply

3. ## Re: Add figures based on 3 criteria from Test Data Sheet

2003 excel file  Register To Reply

4. ## Re: Add figures based on 3 criteria from Test Data Sheet

Does the formula provided work?  Register To Reply

5. ## Re: Add figures based on 3 criteria from Test Data Sheet

Hi NBVC thanks tried it.. didnt seem to give the result expected :S  Register To Reply

6. ## Re: Add figures based on 3 criteria from Test Data Sheet

Opps sorry found the problem formula works great thank you  Register To Reply

7. ## Re: Add figures based on 3 criteria from Test Data Sheet

If you are looking for all months, then you can just use this:

=IF(A10="","",SUMPRODUCT('Test Data'!\$C\$9:\$Z\$26,('Test Data'!\$A\$9:\$A\$26=\$A10)*('Test Data'!\$B\$9:\$B\$26=B\$9)*('Test Data'!\$C\$7:\$Z\$7<>"")))  Register To Reply

8. ## Re: Add figures based on 3 criteria from Test Data Sheet

Hi NBVC could you throw some light as to why the formula isnt working on the test file attached. Plus I also want to be able to apply same conditions to work on second formula but this I want to add the sum of (multiply c10*d10 and so on) to achive the result if you could help.

Thanks  Register To Reply

9. ## Re: Add figures based on 3 criteria from Test Data Sheet

You have DIV/0 errors in the source table, that is why you get the errors here.... you will need to remove those errors...

Try replacing the formulas that have divisors with an IF condition. For example:

=((C11*D11+C13*D13+C15*D15+C17*D17)/C19)

would be

=IF(C19=0,0,((C11*D11+C13*D13+C15*D15+C17*D17)/C19))  Register To Reply

10. ## Re: Add figures based on 3 criteria from Test Data Sheet

Hi I have other sheets which also show Div/0 but it does not show an error in calculations in those.. On this test tab file which is an extract from the live file i changed the source from Test!\$C\$9:\$Z\$30 to Test!\$C\$9:\$R\$30 ``Please Login or Register  to view this content.``
... hence the result is showing #Value!... I'm sure this is simple but i cant seem to get the result.  Register To Reply

11. ## Re: Add figures based on 3 criteria from Test Data Sheet

Sumproduct won't give the DIV/0 error on it's own unless there is an error in the source table.

The Value error might be a matter of text where numbers are expected?? I'd have to see an example of that...  Register To Reply

12. ## Re: Add figures based on 3 criteria from Test Data Sheet

I have attached a file bdm test2.xls in my above post.  Register To Reply

13. ## Re: Add figures based on 3 criteria from Test Data Sheet

The ranges in the SUMPRODUCT are not matching up...
in the first array you id Test!Test!\$C\$9:\$R\$30 then in the Isnumber(Match()) part you id Test!\$C\$7:\$Z\$7

so should be... ``Please Login or Register  to view this content.``
or change both R's to Z's if you want to go to column Z.  Register To Reply

14. ## Re: Add figures based on 3 criteria from Test Data Sheet

Thanks NBVC that worked ....  Register To Reply