|
sumproduct - <
I have a list of 6 criteria but I want to add up 4 of them. When I try to use
< I get a result of 0. This is the formula that isn't working. =-(SUMPRODUCT('YTD ACTUALS Data'!$G$19:$G$5805,('YTD ACTUALS Data'!$A$19:$A$5805=A26)*1,('YTD ACTUALS Data'!$D$19:$D$5805<"R Returns")*1),('YTD ACTUALS Data'!$D$19:$D$5805<"C Create Returns Prov")*1) Is there an efficient way of summing 4 of the 6 without adding the 4 individually? Thanks Rick |
sumproduct - <
Replace criteria1,2,3,4 with the actuals.Would suggest to test this with
smaller amount of data. Try the below and feedback (all in one line) =SUMPRODUCT(--('YTD ACTUALS Data'!$A$19:$A$5805=A26),--ISNUMBER(MATCH('YTD ACTUALS Data'!$D$19:$D$5805,{"criteria1", "criteria2", "criteria3", "criteria4"},0)), 'YTD ACTUALS Data'!$G$19:$G$5805) If this post helps click Yes --------------- Jacob Skaria "Rick" wrote: I have a list of 6 criteria but I want to add up 4 of them. When I try to use < I get a result of 0. This is the formula that isn't working. =-(SUMPRODUCT('YTD ACTUALS Data'!$G$19:$G$5805,('YTD ACTUALS Data'!$A$19:$A$5805=A26)*1,('YTD ACTUALS Data'!$D$19:$D$5805<"R Returns")*1),('YTD ACTUALS Data'!$D$19:$D$5805<"C Create Returns Prov")*1) Is there an efficient way of summing 4 of the 6 without adding the 4 individually? Thanks Rick |
sumproduct - <
A couple of ways.
One that looks more self-explanatory =SUMPRODUCT(--('YTD ACTUALS Data'!$A$19:$A$25=A26), SIGN(('YTD ACTUALS Data'!$D$19:$D$25<"R Returns")*('YTD ACTUALS Data'!$D$19:$D$25<"C Create Returns Prov")), 'YTD ACTUALS Data'!$G$19:$G$25) and one that is tidier but less obvious =SUMPRODUCT(--('YTD ACTUALS Data'!$A$19:$A$25=A26), --NOT(ISNUMBER(MATCH('YTD ACTUALS Data'!$D$19:$D$25,{"R Returns","C Create Returns Prov"},0))), 'YTD ACTUALS Data'!$G$19:$G$25) -- __________________________________ HTH Bob "Rick" wrote in message ... I have a list of 6 criteria but I want to add up 4 of them. When I try to use < I get a result of 0. This is the formula that isn't working. =-(SUMPRODUCT('YTD ACTUALS Data'!$G$19:$G$5805,('YTD ACTUALS Data'!$A$19:$A$5805=A26)*1,('YTD ACTUALS Data'!$D$19:$D$5805<"R Returns")*1),('YTD ACTUALS Data'!$D$19:$D$5805<"C Create Returns Prov")*1) Is there an efficient way of summing 4 of the 6 without adding the 4 individually? Thanks Rick |
sumproduct - <
Thanks that is perfect, but how can I get it to work with 2 or more
contiguous columns? When I try to use ['YTD ACTUALS Data'!$G$19:$H$5805] It returns 0. Thanks Rick "Jacob Skaria" wrote: Replace criteria1,2,3,4 with the actuals.Would suggest to test this with smaller amount of data. Try the below and feedback (all in one line) =SUMPRODUCT(--('YTD ACTUALS Data'!$A$19:$A$5805=A26),--ISNUMBER(MATCH('YTD ACTUALS Data'!$D$19:$D$5805,{"criteria1", "criteria2", "criteria3", "criteria4"},0)), 'YTD ACTUALS Data'!$G$19:$G$5805) If this post helps click Yes --------------- Jacob Skaria "Rick" wrote: I have a list of 6 criteria but I want to add up 4 of them. When I try to use < I get a result of 0. This is the formula that isn't working. =-(SUMPRODUCT('YTD ACTUALS Data'!$G$19:$G$5805,('YTD ACTUALS Data'!$A$19:$A$5805=A26)*1,('YTD ACTUALS Data'!$D$19:$D$5805<"R Returns")*1),('YTD ACTUALS Data'!$D$19:$D$5805<"C Create Returns Prov")*1) Is there an efficient way of summing 4 of the 6 without adding the 4 individually? Thanks Rick |
sumproduct - <
Try the below;
(last argument of previous formula) 'YTD ACTUALS Data'!$G$19:$G$5805+'YTD ACTUALS Data'!$H$19:$H$5805 If this post helps click Yes --------------- Jacob Skaria "Rick" wrote: Thanks that is perfect, but how can I get it to work with 2 or more contiguous columns? When I try to use ['YTD ACTUALS Data'!$G$19:$H$5805] It returns 0. Thanks Rick "Jacob Skaria" wrote: Replace criteria1,2,3,4 with the actuals.Would suggest to test this with smaller amount of data. Try the below and feedback (all in one line) =SUMPRODUCT(--('YTD ACTUALS Data'!$A$19:$A$5805=A26),--ISNUMBER(MATCH('YTD ACTUALS Data'!$D$19:$D$5805,{"criteria1", "criteria2", "criteria3", "criteria4"},0)), 'YTD ACTUALS Data'!$G$19:$G$5805) If this post helps click Yes --------------- Jacob Skaria "Rick" wrote: I have a list of 6 criteria but I want to add up 4 of them. When I try to use < I get a result of 0. This is the formula that isn't working. =-(SUMPRODUCT('YTD ACTUALS Data'!$G$19:$G$5805,('YTD ACTUALS Data'!$A$19:$A$5805=A26)*1,('YTD ACTUALS Data'!$D$19:$D$5805<"R Returns")*1),('YTD ACTUALS Data'!$D$19:$D$5805<"C Create Returns Prov")*1) Is there an efficient way of summing 4 of the 6 without adding the 4 individually? Thanks Rick |
sumproduct - <
Use the * operator in this instance
=SUMPRODUCT(('YTD ACTUALS Data'!$A$19:$A$5805=A26)* (ISNUMBER(MATCH('YTD ACTUALS Data'!$D$19:$D$5805,{"criteria1","criteria2","crit eria3","criteria4"},0))* ('YTD ACTUALS Data'!$G$19:$H$5805))) -- __________________________________ HTH Bob "Rick" wrote in message ... Thanks that is perfect, but how can I get it to work with 2 or more contiguous columns? When I try to use ['YTD ACTUALS Data'!$G$19:$H$5805] It returns 0. Thanks Rick "Jacob Skaria" wrote: Replace criteria1,2,3,4 with the actuals.Would suggest to test this with smaller amount of data. Try the below and feedback (all in one line) =SUMPRODUCT(--('YTD ACTUALS Data'!$A$19:$A$5805=A26),--ISNUMBER(MATCH('YTD ACTUALS Data'!$D$19:$D$5805,{"criteria1", "criteria2", "criteria3", "criteria4"},0)), 'YTD ACTUALS Data'!$G$19:$G$5805) If this post helps click Yes --------------- Jacob Skaria "Rick" wrote: I have a list of 6 criteria but I want to add up 4 of them. When I try to use < I get a result of 0. This is the formula that isn't working. =-(SUMPRODUCT('YTD ACTUALS Data'!$G$19:$G$5805,('YTD ACTUALS Data'!$A$19:$A$5805=A26)*1,('YTD ACTUALS Data'!$D$19:$D$5805<"R Returns")*1),('YTD ACTUALS Data'!$D$19:$D$5805<"C Create Returns Prov")*1) Is there an efficient way of summing 4 of the 6 without adding the 4 individually? Thanks Rick |
| All times are GMT +1. The time now is 12:34 AM. |
Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
ExcelBanter.com