| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
SUM Functions with multiple criteria
I have been using the following formula for adding certain cells based on two
criteria: =SUMPRODUCT(--($H$3:$H$1352="PRODUCT TYPE"),--($J$3:$J$1352="NEW"),($Q$3:$Q$1352)) This has functioned fine, however, now I need to add another criteria - ex - I need to include ($F$3:$F$1352="AP") to the formula as an additional criteria to filter before adding Q3:Q1352. When I try to add this to the formula above, I get #REF!. Any suggestions on how to properly format the above formula to include the additional criteria would be greatly appreciated. |
|
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
SUM Functions with multiple criteria
Hi,
Just can't think of any reason why the formula would return #REF. Try this: =SUMPRODUCT(($H$3:$H$1352="PRODUCT TYPE")*($J$3:$J$1352="NEW")*($F$3:$F$1352="AP"),$Q $3:$Q$1352) or =SUMPRODUCT(--($H$3:$H$1352="PRODUCT TYPE"),--($J$3:$J$1352="NEW"),--($F$3:$F$1352="AP"),$Q$3:$Q$1352) HTH Jean-Guy "readystate" wrote: I have been using the following formula for adding certain cells based on two criteria: =SUMPRODUCT(--($H$3:$H$1352="PRODUCT TYPE"),--($J$3:$J$1352="NEW"),(`) This has functioned fine, however, now I need to add another criteria - ex - I need to include ($F$3:$F$1352="AP") to the formula as an additional criteria to filter before adding Q3:Q1352. When I try to add this to the formula above, I get #REF!. Any suggestions on how to properly format the above formula to include the additional criteria would be greatly appreciated. |
|
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
SUM Functions with multiple criteria
Both suggestions worked great. Thanks for the help!!!
Have a great holiday. "pinmaster" wrote: Hi, Just can't think of any reason why the formula would return #REF. Try this: =SUMPRODUCT(($H$3:$H$1352="PRODUCT TYPE")*($J$3:$J$1352="NEW")*($F$3:$F$1352="AP"),$Q $3:$Q$1352) or =SUMPRODUCT(--($H$3:$H$1352="PRODUCT TYPE"),--($J$3:$J$1352="NEW"),--($F$3:$F$1352="AP"),$Q$3:$Q$1352) HTH Jean-Guy "readystate" wrote: I have been using the following formula for adding certain cells based on two criteria: =SUMPRODUCT(--($H$3:$H$1352="PRODUCT TYPE"),--($J$3:$J$1352="NEW"),(`) This has functioned fine, however, now I need to add another criteria - ex - I need to include ($F$3:$F$1352="AP") to the formula as an additional criteria to filter before adding Q3:Q1352. When I try to add this to the formula above, I get #REF!. Any suggestions on how to properly format the above formula to include the additional criteria would be greatly appreciated. |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| match multiple criteria ina range from multiple criteria multiplet | Excel Worksheet Functions | |||
| Index & Match functions - multiple criteria and multiple results | Excel Worksheet Functions | |||
| How do you create COUNTIF functions based on multiple criteria? | Excel Worksheet Functions | |||
| Counting Cells with multiple criteria.One criteria supporting wild | Excel Worksheet Functions | |||
| Counting Cells with multiple criteria.One criteria supporting wild | Excel Worksheet Functions | |||
Linear Mode
