Using SUMIF, ROUNDDOWN Together

[robbiev]

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

 

[PaddyD]

you just want the total for the lines that have both ATM & Fee?

Try a sumproduct:

Book7
	A	B	C	D	E
1	Date	#	Payee	Amount	Balance
2	8-Jun	ATM	BNH	-$41.50	$246.36
3	8-Jun	ATM	Fee	-$0.75	$245.61
4	8-Jun	VISA	Texaco	-$17.00	$228.61
5	8-Jul	ATM	Walpole	-$20.00	$208.61
6	8-Jul	ATM	Fee	-$0.75	$207.86
7
8		ATMFees:	-$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.

Paddy

 

[robbiev]

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

 

[PaddyD]

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.

 

[PaddyD]

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

Paddy

 

[robbiev]

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

 

[PaddyD]

Book7
	A	B	C	D	E
1	Date	#	Payee	Amount	Balance
2	8-Jun	ATM	BNH	-$41.50	$246.36
3	8-Jun	ATM	Fee	-$0.75	$245.61
4	8-Jun	VISA	Texaco	-$17.00	$228.61
5	8-Jul	ATM	Walpole	-$20.00	$208.61
6	8-Jul	ATM	BNH	-$31.50	$208.61
7
8			Result:	$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.

Paddy