SUMIFS based on text criteria between selected dates

[samlofty]

=SUMIFS($K$17:$L$1000,$G$17:$G$1000,"win**",$B$17:$B$1000,">="&$R$663,$B$17:$B$1000,"<="&$R$664)

K:L range I want to sum in [h]:mm
G Text condition
B date range. I can select dates in R663 and R664

I have tried the text condition (G) at the end but both returns #VALUE!. Where have I gone wrong?

Thanks in anticipation

 

[Fluff]

Hi & welcome to MrExcel.
All the ranges in a Sumifs need to be the same size, but your initial range is 2 columns & the others are 1 column.
What does col G contain?

 

[DanteAmor]

K:L range I want to sum

Why K and L?
That is why the error. If you have the cells combined, use only K.


=SUMIFS($K$17:$K$1000,$G$17:$G$1000,"win**",$B$17:$B$1000,">="&$R$663,$B$17:$B$1000,"<="&$R$664)

 

[samlofty]

Why K and L?
That is why the error. If you have the cells combined, use only K.


=SUMIFS($K$17:$K$1000,$G$17:$G$1000,"win**",$B$17:$B$1000,">="&$R$663,$B$17:$B$1000,"<="&$R$664)

Many thanks. The data to be summed is in both Columns K and L. I'll adjust so that I get both.

 

[Fluff]

Could you answer my question from post#2?

 

[samlofty]

Could you answer my question from post#2?

Apologies if I didn't see the 2nd. Column G is text string, in this case only text containing "win**"

 

[DanteAmor]

If you are going to have a single column for the text "G" and a single column for the date, then try

=SUMIFS($K$17:$K$1000,$G$17:$G$1000,"win*",$B$17:$B$1000,">="&$R$663,$B$17:$B$1000,"<="&$R$664)+ SUMIFS($L$17:$L$1000,$G$17:$G$1000,"win*",$B$17:$B$1000,">="&$R$663,$B$17:$B$1000,"<="&$R$664)

 

[Fluff]

Ok, in that case you can use sumproduct.
=SUMPRODUCT(($G$17:$G$1000="win**")*($B$17:$B$1000>=A16)*($B$17:$B$1000<=B16)*($K$17:$L$1000))

I wasn't sure if the asterix were being used as a wild card, or not.