sumproduct - <>

[Rick]

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

 

[Jacob Skaria]

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

 

[Bob Phillips]

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)

 

[Rick]

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]

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

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

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

 

[Bob Phillips]

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","criteria3","criteria4"},0))*
('YTD ACTUALS Data'!$G$19:$H$5805)))

--
__________________________________
HTH

Bob

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

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