-2

I have a SUMIFS formula below and it does not seem to be working correctly any help would be greatly appreciated.

The formula is below 

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

From the picture you can see that the result in cell R4 should read 4 but it returns a 0

Any help would be greatly appreciated.

enter image description here

Philip Connell
  • 651
  • 5
  • 25
  • 53

2 Answers2

1

SumIfs works with sum_range, criteria_range, criteria.

You've given it sum_range, criteria_range, criteria_cell & criteria.

Try =SUMIFS(C4:N4,C4:N4,"<=1"), or as it's only a single criteria =SUMIF(C4:N4,"<=1",C4:N4)

Edit: Just noticed - is that first criteria meant to be <=11?
If so this formula will work: =SUM(IF(C4<=11,C4,0),SUMIF(F4:N4,"<=1",F4:N4))

Darren Bartrup-Cook
  • 18,362
  • 1
  • 23
  • 45
  • Thank you for the help `=SUM(IF(C4<=11,C4,0),SUMIF(F4:N4,"<=1",F4:N4))` works really well. I was wondering if I may set you another challenge 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 . – Philip Connell Oct 04 '16 at 11:11
  • 1
    Off the top of my head this would work: `=SUM(IF(AND(C4>=11,C4<=19),C4,0),IF(AND(F4>=2,F4<=20),F4,0),IF(AND(I4>=2,I4<=20),I4,0),IF(AND(L4>=2,L4<=20),L4,0))`. I've got a feeling there should be a better way though. – Darren Bartrup-Cook Oct 04 '16 at 12:10
  • Unfortunately `=SUM(IF(AND(C4>=11,C4<=19),C4,0),IF(AND(F4>=2,F4<=20),F4,0),‌​IF(AND(I4>=2,I4<=20)‌​,I4,0),IF(AND(L4>=2,‌​L4<=20),L4,0))` Thank you for the attempt though. Very much appreciated. – Philip Connell Oct 04 '16 at 14:08
  • Not sure what's unfortunate.... Didn't it work as you'd planned? Actually - I just copied it and pasted and there were some strange I bars placed in the formula with invisible characters so it gave a _formula contains errors_ message. Each part of the formula should be in the format `IF(AND(C4>=11,C4<=19),C4,0)` - if between 11 & 19 inclusive then use the value of C4 in the sum, otherwise use 0. Then just sum the results. – Darren Bartrup-Cook Oct 04 '16 at 15:03
  • @ Darren Bartrup-Cook Thanks for the help this is what ended up working for me. `=SUM(AND(C4>=12,C4<=19)*C4,AND(F4>=2,F4<=20)*F4,AND(I4>=2,I4<=20)*I4,AND(L4>=2,L4<=20)*L4)` Thanks so much for all the help. I hope you got some good Stackoverflow points from today. Much respect from Dublin :-) – Philip Connell Oct 04 '16 at 15:19
  • 1
    That'll work - `AND(C4>=12, C4<=19)` will return TRUE (1) or FALSE (0) it then multiplies by the value in C4 so either C4*1 or C4*0. :) – Darren Bartrup-Cook Oct 04 '16 at 15:27
1

Using SUMIFS means that you want to add the values in C4:N4 only if all conditions are TRUE. If this is correct use this formula.

=IF(AND(C4<=11,F4<=1,I4<=1,L4<=1),SUM(C4:N4),0)

'SUMIFS' is intended for operations with ranges in which one column is added if all condition in other columns are met see WorksheetFunction.SumIfs Method . But in your case you are testing conditions in a row and if they are met you want to add the values in the row.

EEM
  • 6,601
  • 2
  • 18
  • 33
  • That formula will fail if one of the values is FALSE. If `F4` is 2 the formula returns 0 rather than 3 (I think that's how the OP wants it to work). The columns/rows doesn't matter with SUMIFS, as the help page states: _Each criteria_range does not have to be the same size and shape as sum_range. The actual cells that are added are determined by using the top, left cell in that criteria_range as the beginning cell, and then including cells that correspond in size and shape to sum_range._ (which is confusing in itself). – Darren Bartrup-Cook Oct 04 '16 at 10:12
  • @DarrenBartrup-Cook it seems that you did not read my first statement... – EEM Oct 04 '16 at 11:19
  • I read it, just misunderstood it. SUMIFS will add the values in C4:N4 that meet all the criteria for that cell, or if you prefer, each cell in sum_range is summed only if all of the corresponding criteria specified are true for that cell. – Darren Bartrup-Cook Oct 04 '16 at 12:03