How do I calculate a column based on two criteria?

[charlesstricklin]

I have been fiddling with this for an hour, mostly using SUMIFS but I'm not getting anywhere. Assume I present the following data:

	A`	B	C
1​	$ 81.00	Expense	Personal
2​	$ 75.00	Expense	Personal
3​	$ 25.00	Expense	Personal
4​	$ 42.00	Expense	Business
5​	$ 14.00	Expense	Personal
6​	$ 59.00	Income	Personal
7​	$ 71.00	Expense	Business
8​	$ 70.00	Expense	Business
9​	$ 91.00	Expense	Personal
10​	$ 88.00	Income	Business
11​	$ 48.00	Income	Business
12​	$ 4.00	Expense	Personal
13​	$ 82.00	Expense	Business
14​	$ 58.00	Expense	Business
15​	$ 49.00	Expense	Personal
16​	$ 71.00	Expense	Personal​
17​	$ 8.00	Expense	Personal
18​	$ 88.00	Income	Personal
19​	$ 3.00	Expense	Personal
20​	$ 78.00	Income	Personal

How do I calculate Business Income, Business Expenses, Personal Profit/Loss, Total Income, etc.?

 

[etaf]

how about
=SUMIFS(A1:A20,B1:B20,D2,C1:C20,E2)
where D2 and E2 are the criteria

how do you define Profit/loss???
total income - is that Business income
if so its in example below

Book1
	A	B	C	D	E	F
1	81	Expense	Personal
2	75	Expense	Personal	Expense	Personal	350
3	25	Expense	Personal	Income	Personal	225
4	42	Expense	Business	Expense	Business	323
5	14	Expense	Personal	Income	Business	136
6	59	Income	Personal			0
7	71	Expense	Business			0
8	70	Expense	Business			0
9	91	Expense	Personal
10	88	Income	Business
11	48	Income	Business
12	4	Expense	Personal
13	82	Expense	Business
14	58	Expense	Business
15	49	Expense	Personal
16	71	Expense	Personal
17	8	Expense	Personal
18	88	Income	Personal
19	3	Expense	Personal
20	78	Income	Personal
Sheet1

Cell Formulas
Range	Formula
F2:F8	F2	=SUMIFS(A1:A20,B1:B20,D2,C1:C20,E2)

 

[Joe4]

Should be a pretty straightforward SUMIFS formula.

For Business Income the formula would be:

=SUMIFS(A1:A20,B1:B20,"Income",C1:C20,"Business")

and repeat the same logic for the other ones.

 

[6StringJazzer]

This could be done with SUMIFS but I would use a pivot table.

$scratch.xlsm
	A	B	C	D	E	F	G
1	Amount	Type	Personal/Business		Sum of Amount	Column Labels
2	$ 81.00	Expense	Personal		Row Labels	Business	Personal
3	$ 75.00	Expense	Personal		Expense	323	421
4	$ 25.00	Expense	Personal		Income	136	225
5	$ 42.00	Expense	Business
6	$ 14.00	Expense	Personal
7	$ 59.00	Income	Personal
8	$ 71.00	Expense	Business
9	$ 70.00	Expense	Business
10	$ 91.00	Expense	Personal
11	$ 88.00	Income	Business
12	$ 48.00	Income	Business
13	$ 4.00	Expense	Personal
14	$ 82.00	Expense	Business
15	$ 58.00	Expense	Business
16	$ 49.00	Expense	Personal
17	$ 71.00	Expense	Personal
18	$ 8.00	Expense	Personal
19	$ 88.00	Income	Personal
20	$ 3.00	Expense	Personal
21	$ 78.00	Income	Personal
Sheet15

 

[alansidman]

A Power Query Solution

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Column1", Currency.Type}}),
    #"Grouped Rows" = Table.Group(#"Changed Type", {"Column2", "Column3"}, {{"Total", each List.Sum([Column1]), type nullable number}})
in
    #"Grouped Rows"

Book4
	A	B	C	D	E	F	G
1	Column1	Column2	Column3		Column2	Column3	Total
2	$81.00	Expense	Personal		Expense	Personal	350
3	$75.00	Expense	Personal		Expense	Business	323
4	$25.00	Expense	Personal		Income	Personal	225
5	$42.00	Expense	Business		Income	Business	136
6	$14.00	Expense	Personal		Expense	Personal	71
7	$59.00	Income	Personal
8	$71.00	Expense	Business
9	$70.00	Expense	Business
10	$91.00	Expense	Personal
11	$88.00	Income	Business
12	$48.00	Income	Business
13	$4.00	Expense	Personal
14	$82.00	Expense	Business
15	$58.00	Expense	Business
16	$49.00	Expense	Personal
17	$71.00	Expense	Personal
18	$8.00	Expense	Personal
19	$88.00	Income	Personal
20	$3.00	Expense	Personal
21	$78.00	Income	Personal
Sheet1

 

[alansidman]

I know the OP is still on 2010, but for anyone else that is using O365, here is the new GroupBy formula

=GROUPBY(Table1[[Column2]:[Column3]],Table1[Column1],SUM)

and the result looks as follows:

Expense	Business	323
Expense	Personal	350
Expense	Personal	71
Income	Business	136
Income	Personal	225
Total		1105

 

[6StringJazzer]

You seem to have some kind of stray character on row 16. Delete the cell with "Personal" and type it in again. Maybe that is messing with your formulas.

 

[6StringJazzer]

I know the OP is still on 2010, but for anyone else that is using O365, here is the new GroupBy formula

=GROUPBY(Table1[[Column2]:[Column3]],Table1[Column1],SUM)

GROUPBY is not available on all versions of 365. I think it's only available to Insiders at the moment. (Also true for PIVOTBY)

 

[alansidman]

thanks Jeff. Not sure what it was, but the codes remain the same and the data updates to the correct amounts

 

[charlesstricklin]

Should be a pretty straightforward SUMIFS formula.

For Business Income the formula would be:

=SUMIFS(A1:A20,B1:B20,"Income",C1:C20,"Business")

and repeat the same logic for the other ones.

Thanks!