sumproduct with multiple criteria

  • Thread starter Thread starter ryanb.
  • Start date Start date
R

ryanb.

is something like this possible (multiple criteria in the braces)? I know
it does not work currently, but I am hoping there is a way to do something
like this without adding 2 sumproduct formulas.

=SUMPRODUCT(--(QUERY!$A$2:$A$31311={$B16,$C16}),--(QUERY!$F$2:$F$31311=J$3),
--(QUERY!$G$2:$G$31311<=T50MONTH),--(QUERY!$H$2:$H$31311))


TIA,
 
It looks like you want a count where all criteria are met.
I can't tell what the -- is getting at unless it's for legibility,
but assuming it's not trying to do something unexpected
and that the multiple criteria is an OR situation,
then this should work (with some extra spaces)
={SUM(((QUERY!$A$2:$A$31311=$B16)+(QUERY!$A$2:$A$31311=$C16))
* (QUERY!$F$2:$F$31311=J$3)
* (QUERY!$G$2:$G$31311<=T50MONTH)
* (QUERY!$H$2:$H$31311))
* IF(B16=C16,.5,1)}

*if(B16=C16,.5,1) at the end prevents the double count if B16=C16.
{} indicates an array formula.

If this doesn't help, I would need an explanation of what the formula is
trying to do.

ken c
 
What's not working. The {b16,c16} just means that if your A range has either
then OK to count it?
same thing as writing
((QUERY!$A$2:$A$31311=$B16)+(QUERY!$A$2:$A$31311=$c16)).

Are you trying to get a month out the G range formatted as dates. If so use
month(QUERY!$G$2:$G$31311)<=T50MONTH

Try breaking your formula down into each component to test.
 
I don't mean to hijack the thread, but your response is interesting, and
confusing.
In what context can {b16,c16} be used like an OR?
If I make a very simple example
A2=2 B2=2
A3=3 B3=5
A4=4

and in B4, I want a count for the number of time B2 or B3 appear in the
range A2:A4
Depending on my mood, I might use:
=COUNTIF(A2:A4,B2)+COUNTIF(A2:A4,B3) -or-
={SUM((A2:A4=B2)*1,(A2:A4=B3)*1)}
Or maybe if I was testing only one set on criteria at a time, I would add a
label in A1 and B1 and use
=DCOUNT(A1:A4,A1,B1:B3)

but I cant think of a way to use {B2,B3} in any test condition.
 
You can use

=SUM(--(A2:A4=TRANSPOSE(B2:B3)))

entered with ctrl + shift & enter

or entered normally like

=SUMPRODUCT(COUNTIF(A2:A4,B2:B3))

or you can hard code it like

=SUMPRODUCT(--(A2:A4={2,5}))

entered normally



--
Regards,

Peo Sjoblom

(No private emails please, for everyone's
benefit keep the discussion in the newsgroup/forum)
 
Don,

Currently I am using the following formula and it works, thought the
worksheet is extremely slow (the formula is in 4 columns on approximately
1300 rows... two columns <=T50MONTH and two =T50MONTH). I saw a response to
another sumproduct question where {} were used for multiple criteria. I was
curious as to whether the {} could be used to reference multiple cells.

=SUMPRODUCT(--(QUERY!$A$2:$A$31311=$B16),--(QUERY!$F$2:$F$31311=J$3),--(QUER
Y!$G$2:$G$31311<=T50MONTH),--(QUERY!$H$2:$H$31311))+SUMPRODUCT(--(QUERY!$A$2
:$A$31311=$C16),--(QUERY!$F$2:$F$31311=J$3),--(QUERY!$G$2:$G$31311<=T50MONTH
),--(QUERY!$H$2:$H$31311))

This works fine (but slow), but I was wondering if there was a way to do it
without adding sumproducts, thus possibly speeding it up a bit.

When I tried to use:

=SUMPRODUCT(--(QUERY!$A$2:$A$31311={$B16,$C16}),--(QUERY!$F$2:$F$31311=J$3),
--(QUERY!$G$2:$G$31311<=T50MONTH),--(QUERY!$H$2:$H$31311))

it does not work, and I am guessing it is because I am not using the
{}correctly, though I am not very familiar with {} and cannot be sure.

The formula sums a customers sales by month by year, and each customer has 2
customer id's (one for product purchases, and a separate account for large
purchases put on a note: columns B and C). Row 3 contains the year for each
column. Column H on the QUERY tab contains the sales dollars.

Thanks your help in advance,
 
You learn something new everyday.
I didn't know that sumproduct could work on array formulas
without being entered with ctrl+shift+enter.
Are there other functions for which that is true?
 
kcc said:
You learn something new everyday.
I didn't know that sumproduct could work on array formulas
without being entered with ctrl+shift+enter.
Are there other functions for which that is true?
Click to expand...

Yes indeed; many. INDEX, MATCH, SUM, MAX and MIN are a few that come
readily to mind.

Alan Beban
 
Seems to be true only part of the time. From the example I showed:
=SUMPRODUCT(--(A2:A4={2,5})) works, but
=SUM(--(A2:A4={2,5})) doesn't work (unless you use ctrl+shift+enter).
On the other hand,
=SUM(--(TRANSPOSE({2,3,4})={2,5})) does work.

I can't get SUM to work with arrays unless the formula only contains
constants.
I can't think of examples of when I would use array formulas with the other
functions listed.
 
kcc said:
I can't get SUM to work with arrays unless the formula only contains
constants.
I can't think of examples of when I would use array formulas with the other
functions listed.
Click to expand...

With names (Including Smith) in A2:A6 and weekly scores in B2:E6, how
would you return the maximum score for any particular name (e.g., Smith)?

Alan Beban
 
Back
Top