SUMIF formula question

[jdotbonk]

Greetings,

My goal here is to add up grand totals at the top of the spreadsheet for total dollar amount. I already got the miles figured out....

I am wondering how to sum up the amounts below containing $ only. Is this possible? Or would it be possible to add up all of the values in column 1 excluding cells that have the word "miles" in column 2.

Any assistance is appreciated!
Cheers

amount	category		200​	total miles
50​	miles​		total $ from amount column 1??????? formula???	total $
50​	miles​
50​	miles​
$ 10.00	meal​
$ 20.00	supplies​
$ 30.00	car​
$ 100.00	travel​
50​	miles​

 

[jtakw]

Hi,

Use SUMIF:

Book3.xlsx
	A	B	C	D	E
1	amount	category		200	total miles
2	50	miles		160	total $
3	50	miles
4	50	miles
5	$10.00	meal
6	$20.00	supplies
7	$30.00	car
8	$100.00	travel
9	50	miles
Sheet1024

Cell Formulas
Range	Formula
D2	D2	=SUMIF(B2:B9,"<>miles",A2:A9)

 

[Peter_SSs]

Welcome to the MrExcel board!

Assuming those values with $ signs are actually numbers, formatted as currency then you could use either of the methods you mentioned.

22 02 27.xlsm
	A	B	C	D	E
1	amount	category		200	total miles
2	50	miles		160	total $
3	50	miles		160
4	50	miles
5	$10.00	meal
6	$20.00	supplies
7	$30.00	car
8	$100.00	travel
9	50	miles
Sums

Cell Formulas
Range	Formula
D1	D1	=SUMIF(B2:B9,"miles",A2:A9)
D2	D2	=SUMIF(B2:B9,"<>miles",A2:A9)
D3	D3	=SUM(A2:A9)-D1

If the "$" values are text (looks a bit like they might be since they are left-aligned, then try

22 02 27.xlsm
	A	B	C	D	E
1	amount	category		200	total miles
2	50	miles		160	total $
3	50	miles
4	50	miles
5	$10.00	meal
6	$20.00	supplies
7	$30.00	car
8	$100.00	travel
9	50	miles
Sums (2)

Cell Formulas
Range	Formula
D1	D1	=SUMIF(B2:B9,"miles",A2:A9)
D2	D2	=SUMPRODUCT(--(LEFT(A2:A9,1)="$"),--SUBSTITUTE(A2:A9,"$",""))

 

[jdotbonk]

Hi,

Use SUMIF:

Book3.xlsx
	A	B	C	D	E
1	amount	category		200	total miles
2	50	miles		160	total $
3	50	miles
4	50	miles
5	$10.00	meal
6	$20.00	supplies
7	$30.00	car
8	$100.00	travel
9	50	miles
Sheet1024

Cell Formulas
Range	Formula
D2	D2	=SUMIF(B2:B9,"<>miles",A2:A9)

Thanks much sir!
Cheers

 

[jdotbonk]

Welcome to the MrExcel board!

Assuming those values with $ signs are actually numbers, formatted as currency then you could use either of the methods you mentioned.

22 02 27.xlsm
	A	B	C	D	E
1	amount	category		200	total miles
2	50	miles		160	total $
3	50	miles		160
4	50	miles
5	$10.00	meal
6	$20.00	supplies
7	$30.00	car
8	$100.00	travel
9	50	miles
Sums

Cell Formulas
Range	Formula
D1	D1	=SUMIF(B2:B9,"miles",A2:A9)
D2	D2	=SUMIF(B2:B9,"<>miles",A2:A9)
D3	D3	=SUM(A2:A9)-D1

If the "$" values are text (looks a bit like they might be since they are left-aligned, then try

22 02 27.xlsm
	A	B	C	D	E
1	amount	category		200	total miles
2	50	miles		160	total $
3	50	miles
4	50	miles
5	$10.00	meal
6	$20.00	supplies
7	$30.00	car
8	$100.00	travel
9	50	miles
Sums (2)

Cell Formulas
Range	Formula
D1	D1	=SUMIF(B2:B9,"miles",A2:A9)
D2	D2	=SUMPRODUCT(--(LEFT(A2:A9,1)="$"),--SUBSTITUTE(A2:A9,"$",""))

Thank you very much, I appreciate it!
Cheers!

 

[jtakw]

You're welcome, thanks for the feedback.

@Peter_SSs when I copied and pasted OP's table into Excel, it turned opposite, the "miles" #s were Text, and the $# were Real numbers. Might have something to do with copying on the forum?

 

[Peter_SSs]

Might have something to do with copying on the forum?

I don't think we can read much into it when the data is posted directly in a grid like that. I should have realised that when I mentioned the left-aligned values as a simple grid like that will always left-align anything with text characters. When I copy/pasted from the post all column A turned up as numerical in my sheet.