SUM NOT WORKING WITH MULTIPLE CONDITIONS

navyaa

Board Regular
Joined
Jul 7, 2002
Messages
223
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

Who is Mr Spreadsheet?
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
 
Upvote 0
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.
 
Upvote 0
Aladin,

Thanks for correct me with the conditions :wink:

Time to hit the bed...

Kind regards,
Dennis
 
Upvote 0
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.

we have to install it and add it as addin.

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.
 
Upvote 0
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
 
Upvote 0
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.
 
Upvote 0
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.
 
Upvote 0

Forum statistics

Threads
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.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

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

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

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
Back
Top