# Using SUMIF, ROUNDDOWN Together

#### robbiev

##### New Member
OK, first of all thanks to Paddy for your help so far...here's another question...
What i would like to do with the following example is use the rounddown function to round all occurances of "ATM" in column B that are greater than 4.99, down 1 decimal place (ie \$41.50 to \$40.00) then use the sumif function to subtract the actual total of all "ATM" transactions, to get a total of the fee's that I'm paying at the ATM to the banks atm itself. In the example below, the total should be \$1.50..can I do this?
Thanks! Robbie

A B C D E
Date # Payee Amount Balance
8/6 ATM BNH (\$41.50) \$246.36
8/6 ATM Fee (\$0.75) \$245.61
8/6 VISA Texaco (\$17.00) \$228.61
8/7 ATM Walpole (\$20.00) \$208.61
8/7 ATM Fee (\$0.75) \$207.86

### Excel Facts

Square and cube roots
The =SQRT(25) is a square root. For a cube root, use =125^(1/3). For a fourth root, use =625^(1/4).
you just want the total for the lines that have both ATM & Fee?

Try a sumproduct:
Book7
ABCDE
1Date#PayeeAmountBalance
28-JunATMBNH-\$41.50\$246.36
38-JunATMFee-\$0.75\$245.61
48-JunVISATexaco-\$17.00\$228.61
58-JulATMWalpole-\$20.00\$208.61
68-JulATMFee-\$0.75\$207.86
7
8ATMFees:-\$1.50
9
Sheet2

see here for extensive info on ow this is working:

http://www.mrexcel.com/wwwboard/messages/8961.html

Post back if this isn't what you mean / if you need more.

Although i see how you came up with the answer here, it's not the answer for what i'm looking for. If you look at cell D2, you'll notice that there's and ATM withdrawal for \$41.50...what i would like the function to do, is go through, find all the atm transactions greater than 4.99 (since it's possible to withdrawl \$5 at some atms), this will also exclude the 2 \$0.75 fee's you see...then what I want the function to do, is round down that \$41.50 to \$40.00, etc for the rest of them, then add all of the rounded numbers together (in this example it would be \$40.00), then finally subtract the total of all the actual ATM withdrawls (\$41.50), thus getting the answer of \$1.50 in the end...let me change my example sheet a bit (I would use the HTML utility to make it look nice, but the comoputer i'm on won't let me)...

A B C D E
Date # Payee Amount Balance
8-Jun ATM BNH -\$41.50 \$246.36
8-Jun ATM Fee -\$0.75 \$245.61
8-Jun VISA Texaco -\$17.00 \$228.61
8-Jul ATM Walpole -\$20.00 \$208.61

the correct answer to this would give you \$1.50

Thanks! Robbie

first thing - if you put html tags above & below your pseudo-data, you can preserve your formatting. see the fag's section for the pre tags.

in your first post you said you wanted to round-down "1 decimal place (ie \$41.50 to \$40.00)". That's not one decimal place! Do you mean round down to the nearest multiple of 10, of 5 ??

yes...my bad...sorry not decimel place, next 10..hehe...but do you understand what i'm trying to do here....
Thanks! Robbie

Book7
ABCDE
1Date#PayeeAmountBalance
28-JunATMBNH-\$41.50\$246.36
38-JunATMFee-\$0.75\$245.61
48-JunVISATexaco-\$17.00\$228.61
58-JulATMWalpole-\$20.00\$208.61
68-JulATMBNH-\$31.50\$208.61
7
8Result:\$3.00
9
Sheet2

=SUMPRODUCT((B2:B6="ATM")*(D2:D6<=-5)*(FLOOR(D2:D6,-10)))-SUMPRODUCT((B2:B6="ATM")*(D2:D6<=-5)*(D2:D6))

this returns a +ve number. just swap the 2 sides of the subtraction around if you want a -ve.

Replies
3
Views
631

1,207,096
Messages
6,076,555
Members
446,213
Latest member
bettigb

### 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