Multiple criteria list in SUMIFS formula

[redsockspugie]

I was wondering whether it is possible to incorporate multiple criteria in a SUMIFS formula.
I have a form control dropdown box with various items in it that have been linked to a list of cells within the spreadsheet. So my dropdown box has, for example: Item 1, Item 2, Item 3, Item 4, All Items. And I have a cell in my spreadsheet which displays which option has been selected.

The formula I am trying to create performs a SUMIF for values (that occur within certain dates) when 'all items' is selected.

This is the sort of thing I am trying to do:
=SUMIFS(PTS!T:T,PTS!B:B,">="&DATE(('Dropdown options'!I6), 1, 1),PTS!B:B,"<="&DATE(('Dropdown options'!I6), 2, 29),PTS!F:F, {item 1, item 2, item 3, etc.})

Ideally, what I would like to do is have this list in another cell and have it referenced in this.
e.g.
=SUMIFS(PTS!T:T,PTS!B:B,">="&DATE(('Dropdown options'!I6), 1, 1),PTS!B:B,"<="&DATE(('Dropdown options'!I6), 2, 29),PTS!F:F, 'Dropdown options'!J4)

where 'Dropdown options'!J4 contained the list of items.

I have also tried using the OR function for the criteria list, but to no avail.
Is this at all possible? Any responses would be very much appreciated. Thanks in advance!

 

[theBardd]

Try using

=SUMPRODUCT((PTS!B:B>=DATE('Dropdown options'!I6,1,1))*(PTS!B:B<=DATE('Dropdown options'!I6,2,29))*(PTS!F:F={"item 1","item 2","item 3"})*(PTS!T:T))

 

[theBardd]

You could also use

=SUM(SUMIFS(PTS!T:T,PTS!B:B,">="&DATE('Dropdown options'!I6,1,1),PTS!B:B,"<="&DATE('Dropdown options'!I6,2,29),PTS!F:F,{"item 1","item 2","item 3"}))

 

[Aladin Akyurek]

I was wondering whether it is possible to incorporate multiple criteria in a SUMIFS formula.
I have a form control dropdown box with various items in it that have been linked to a list of cells within the spreadsheet. So my dropdown box has, for example: Item 1, Item 2, Item 3, Item 4, All Items. And I have a cell in my spreadsheet which displays which option has been selected.

The formula I am trying to create performs a SUMIF for values (that occur within certain dates) when 'all items' is selected.

This is the sort of thing I am trying to do:
=SUMIFS(PTS!T:T,PTS!B:B,">="&DATE(('Dropdown options'!I6), 1, 1),PTS!B:B,"<="&DATE(('Dropdown options'!I6), 2, 29),PTS!F:F, {item 1, item 2, item 3, etc.})

Ideally, what I would like to do is have this list in another cell and have it referenced in this.
e.g.
=SUMIFS(PTS!T:T,PTS!B:B,">="&DATE(('Dropdown options'!I6), 1, 1),PTS!B:B,"<="&DATE(('Dropdown options'!I6), 2, 29),PTS!F:F, 'Dropdown options'!J4)

where 'Dropdown options'!J4 contained the list of items.

I have also tried using the OR function for the criteria list, but to no avail.
Is this at all possible? Any responses would be very much appreciated. Thanks in advance!

1.

=SUM(
  SUMIFS(
   PTS!T:T,
   PTS!B:B,">="&DATE('Dropdown options'!I6, 1, 1),
   PTS!B:B,"<="&DATE('Dropdown options'!I6, 2, 29),
   PTS!F:F, {"item 1", "item 2", "item 3, "}))

2. If the items are in a range, say, in X1:X3...
a)

=SUMPRODUCT(
  SUMIFS(
   PTS!T:T,
   PTS!B:B,">="&DATE('Dropdown options'!I6, 1, 1),
   PTS!B:B,"<="&DATE('Dropdown options'!I6, 2, 29),
   PTS!F:F, X1:X2))

b)

=SUMPRODUCT(
   PTS!T:T,
   --(PTS!B:B>=DATE('Dropdown options'!I6, 1, 1)),
     --(PTS!B:B<=DATE('Dropdown options'!I6, 2, 29)),
   --ISNUMBER(MATCH(PTS!F:F, X1:X2,0)))

 

[Robert Mika]

Create a Named Range with the Item you need and call them list
(let say a1:a3, item1,item2,item3)
then use
=SUMPRODUCT(SUMIFS(pts!T:T,pts!B:B,">="&DATE(('Dropdown options'!I6), 1, 1),pts!B:B,"<="&DATE(('Dropdown options'!I6), 2, 29),pts!F:F,INDIRECT('Dropdown options'!J4)))

 

[redsockspugie]

Thank you for all of your quick replies.

You could also use

=SUM(SUMIFS(PTS!T:T,PTS!B:B,">="&DATE('Dropdown options'!I6,1,1),PTS!B:B,"<="&DATE('Dropdown options'!I6,2,29),PTS!F:F,{"item 1","item 2","item 3"}))

1.

=SUM(
  SUMIFS(
   PTS!T:T,
   PTS!B:B,">="&DATE('Dropdown options'!I6, 1, 1),
   PTS!B:B,"<="&DATE('Dropdown options'!I6, 2, 29),
   PTS!F:F, {"item 1", "item 2", "item 3, "}))

This is the solution I have used so far and it has worked nicely.
Now I would like to have that list in another cell (e.g. 'Dropdown options'!J4) and have it referenced like
=SUM(SUMIFS(PTS!T:T,PTS!B:B,">="&DATE(('Dropdown options'!I6), 1, 1),PTS!B:B,"<="&DATE(('Dropdown options'!I6), 2, 29),PTS!F:F, 'Dropdown options'!J4))
However, just putting {"item 1", "item 2", "item 3, "} into the reference cell is not working.

Create a Named Range with the Item you need and call them list
(let say a1:a3, item1,item2,item3)
then use
=SUMPRODUCT(SUMIFS(pts!T:T,pts!B:B,">="&DATE(('Dropdown options'!I6), 1, 1),pts!B:B,"<="&DATE(('Dropdown options'!I6), 2, 29),pts!F:F,INDIRECT('Dropdown options'!J4)))

Robert Mika, if 'Dropdown options'!J4 ends up representing the list/named range in that one cell, then I think this may be what I am after. However, I am not too familiar with the INDIRECT function and struggling to see how it's been linked to the named range. Would really appreciate if you could explain this please.

Thanks again for all your help!

 

[Robert Mika]

Put "list" in J4.
Then INDIRECT will pick it up and convert to your range.(item1,...)

More on INDIRECT:
http://www.contextures.com/xlFunctions05.html

 

[redsockspugie]

Put "list" in J4.
Then INDIRECT will pick it up and convert to your range.(item1,...)

More on INDIRECT:
http://www.contextures.com/xlFunctions05.html

Fantastic! Thanks for your help!

 

[Robert Mika]

Fantastic! Thanks for your help!

You are welcome.
Thanks for feedback.