Sumifs Formula not working correctly

[Conell8383]

Hi all. I hope you are well and that you can help. I have a SUMIFS formula and it is not working correctly I was hoping that perhaps you may be able to help.

My Formula is this

=SUMIFS(C4:N4,C4:N4,C4<=11,C4:N4,F4<=1,C4:N4,I4<=1,C4:N4,L4<=1)

But it is returning a value of 0 it should be returning a value of 4 Please see my pic for more details.

Any help would be greatly appreciated.

 

[steve the fish]

Try:

=SUM(AND(C4<=11,C4<>""),AND(F4<=1,F4<>""),AND(I4<=1,I4<>""),AND(L4<=1,L4<>""))

 

[Conell8383]

Hi Steve the Fish: =SUM(AND(C4<=11,C4<>""),AND(F4<=1,F4<>""),AND(I4<=1,I4<>""),AND(L4<=1,L4<>"")) kind of worked but if i change the numbers in the cells the number remains 4 it does not change the result. I need it to calculate if C4 is any number below or equal to 11 and the other to be below or equal to 1

Thank you for the help it is greatly appreciated.

 

[wideboydixon]

You've only got 4 values to check, why not try:

=IF($C4<=11,$C4,0)+IF($F4<=1,$F4,0)+IF($I4<=1,$I4,0)+IF($L4<=1,$L4,0)

WBD

 

[steve the fish]

Ah ok try:

=SUM((C4<=11)*C4,(F4<=1)*F4,(I4<=1)*I4,(L4<=1)*L4)

 

[Conell8383]

@ WideboyDixon: Thank you very much this. It worked great. I wonder if I could throw another challenge your way. In Cell S4 I would like to sum or total if C4 is between 11 and 19 and F4 is between 2 and 20 and I4 is between 2 and 20 and L4 is between 2 and 20.

Is this possible? Thanks again for all the help .

 

[Conell8383]

@Steve the Fish: Thank you very much this. It worked great. I wonder if I could throw another challenge your way. In Cell S4 I would like to sum or total if C4 is between 11 and 19 and F4 is between 2 and 20 and I4 is between 2 and 20 and L4 is between 2 and 20.

Is this possible? Thanks again for all the help .

 

[wideboydixon]

Using Steve's pattern:

=SUM(AND(C4>=11,C4<=19)*C4,AND(F4>=2,F4<=20)*F4,AND(I4>=2,I4<=20)*I4,AND(L4>=2,L4<=20)*L4)

Adjust >=, <= and numbers as appropriate.

WBD

 

[Conell8383]

You the man Wideboy Thank you so much for the assistance today. It is greatly appreciated. Much respect from Dublin