SUMIFS to match "from:to" drop down range

[Geebeez]

Hi
I need help to sum a row of data based on a drop down menu "fortnight from" AND a drop down menu "fortnight to".
Range: B3:G3 = numbers to sum
Range: B2:G2 = Fortnight #1 , Fortnight #2 etc
Cell: B15 is drop down menu to "Select Fortnight from:"
Cell: B16 is drop down menu to "Select Fortnight to:"

Example: If B15 = Fortnight #1 and B16 = Fortnight #4 I want to sum cells B3:E3
Example: If B15 = Fortnight #3 and B16 = Fortnight #6 I want to sum cells D3:G3

This is the formula I've tried
=SUMIFS(B3:G3,B2:G2,"="&B15,B2:G2,"="&B16)
I've also tried without "="&
but no luck so far.

First time on forums, would really appreciate some assistance, thanks

 

[jkpieterse]

=sumifs(b3:g3,b2:g2,">="&b15,b2:g2,"<="&b16)

 

[Geebeez]

=sumifs(b3:g3,b2:g2,">="&b15,b2:g2,"<="&b16)

Thank you so very much, all fixed

 

[jkpieterse]

Well, depends. If you really have fortnight #1 , fortnight #2 , fortnight #3 , ... try changing one or two to fortnight #1 0 and fortnight #1 1 and then use a between of fortnight #1 to fortnight #2 . You'll see 10 and 11 are included in the count. This is due to the fact that you are doing a text compare, which works character-by-character.

 

[Geebeez]

Well, depends. If you really have fortnight #1 , fortnight #2 , fortnight #3 , ... try changing one or two to fortnight #1 0 and fortnight #1 1 and then use a between of fortnight #1 to fortnight #2 . You'll see 10 and 11 are included in the count. This is due to the fact that you are doing a text compare, which works character-by-character.

Yes, I couldn't work out why it wasn't working then it suddenly clicked so I changed my fortnights to #01 , #02 etc. Now it works perfectly.
Thanks for your help

 

[jkpieterse]

You're welcome.
NB: Usually quoting text isn't needed, especially if you're responding to the last message since that is next to your response anyway.