# SUM NOT WORKING WITH MULTIPLE CONDITIONS

#### navyaa

##### Board Regular
I have been trying to make this work for the past hour and seems right but is not working.

I have 2 tables one contains account numbers and the other has balances. I need to write a statement to find the sum of the balance column when account no is > 110101016 and <110101029.

This is what i was using but it returns a zero while there are actual values:
=SUM((A2:A20="110101016")*(A2:A20="110101029")*(B2:B20))

### Excel Facts

Author John Walkenbach was Mr Spreadsheet until his retirement in June 2019.
On 2002-08-30 14:55, navyaa wrote:
I have been trying to make this work for the past hour and seems right but is not working.

I have 2 tables one contains account numbers and the other has balances. I need to write a statement to find the sum of the balance column when account no is > 110101016 and <110101029.

This is what i was using but it returns a zero while there are actual values:
=SUM((A2:A20="110101016")*(A2:A20="110101029")*(B2:B20))

Hi,

If You replace the SUM-function with
=SUMPRODUCT((A2:A20="110101016")*(A2:A20="110101029")*(B2:B20)) it will propably work

Kind regards,
Dennis

On 2002-08-30 14:55, navyaa wrote:
I have been trying to make this work for the past hour and seems right but is not working.

I have 2 tables one contains account numbers and the other has balances. I need to write a statement to find the sum of the balance column when account no is > 110101016 and <110101029.

This is what i was using but it returns a zero while there are actual values:
=SUM((A2:A20="110101016")*(A2:A20="110101029")*(B2:B20))

If these account numbers are true numbers (not text), then:

=SUMPRODUCT((A2:A20>110101016)*(A2:A20<110101029),(B2:B20))

will do what you want.

Thanks for correct me with the conditions :wink:

Time to hit the bed...

Kind regards,
Dennis

THANK YOU FOR YOUR HELP GUYS.....

NAVYA.

The best way to solve this type of problem is to use 'conditional sum wizard ' provided by excel.

nisht
ni****h desai
http://www.pexcel.com

On 2002-08-31 00:46, nisht wrote:
The best way to solve this type of problem is to use 'conditional sum wizard ' provided by excel.

Why is that 'the best way' Nisht?

The wizard often creates inefficient array-formulas full of IFs and it has no knowledge of the sumproduct worksheet function.

Hi again,

Sorry to bother you again but i trief the SUMPRODUCT its not working.

I have tried the conditional wizard and SUM and SUMIF too but for some it doesnt seem to reading the values from the range.

I used SUMPRODUCT((G2:G2024>110670999)*(G2:G2024<110672000),(I2:I2024))

Please let me know if there is anything I am doing wrong.

Thank you,
Navya

the formula works for me,
here's a sample:
Book5
ABCDEF
1NumbersValue
21106709571070
311067055810AboveBelow
41106702141011067099911067200070
511067269910
611067101710
711067114210
811067155210
911067173510
1011067226410
1111067270610
1211067286410
1311067072710
1411067193810
1511067012210
1611067043810
1711067270410
1811067156610
1911067281610
2011067082210
2111067155010
2211067026610
2311067203810
2411067291710
Sheet1

You may also want to put the Above/Below values in a different cell.

Thank you for your response Ivan.

I tried using the formula on a new sheet and it does return a value but there seemed to be a problem:

When i changed values in coulumn B(the range to be added) for my sheet just to check whether it works any values i changed in coulmn B would change the answer regardless of whether it fit the criteria or not.And it still doesnt work on my orginal sheet.

Do you think it would be possible for you to look at my excel file or is there anything else I can do because i dont understand why its not working on my page.

Thank you,
Navya.

Replies
1
Views
127
Replies
2
Views
174
Replies
2
Views
129
Replies
6
Views
449
Replies
4
Views
70

1,207,166
Messages
6,076,902
Members
446,239
Latest member
Home Nest

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back