Formula Help

[SDowse]

Hi,

I am trying to do a formula that will sum a column of data based on 2 criteria in another column.

So, E173:E234 contains either "Permanent", "Temporary" or "Perm Risk", and I want to sum range O173:O234 only where "Permanent" or "Perm Risk" appear in column E...

I've tried a few things, but can't get it to work properly...any ideas?

 

[jasonb75]

Assuming nothing else in that column starting with Perm

=SUMIF(E173:E234,"Perm*",O173:O234)

 

[bosco_yip]

=SUMIF(E173:E234,"P*",O173:O234)

Regards

 

[SDowse]

Excellent...many thanks! Simpler than I thought.

 

[jim may]

Qucik Sample....

Excel Workbook
	A	B	C	D	E	F
1	My Text		My Numbers	Proof	28	<<<<
2	abc		1	1
3	ddd		2
4	ccc		3	3
5	ccc		4	4
6	abc		5	5
7	ddd		6
8	abc		7	7
9	ccc		8	8
10	ddd		9
11			45	28
Sheet1

Excel 2007

Cell Formulas
Range	Formula
E1	=SUMPRODUCT(SUMIF(A2:A10,{"abc","ccc"},C2:C10))

 

[jasonb75]

Another alternative, if you had other entries starting with "P" or "Perm" that you didn't want included.

=SUM(SUMIF(E173:E234,{"Perm Risk","Permanent"},O173:O234))

 

[bosco_yip]

Another alternative, if you had other entries starting with "P" or "Perm" that you didn't want included.

=SUM(SUMIF(E173:E234,{"Perm Risk","Permanent"},O173:O234))

Can use 1 function instead of 2 :

=SUMPRODUCT((E173:E234={"Permanent","Perm Risk"})*O173:O234)

Regards

 

[jasonb75]

Can use 1 function instead of 2 :

=SUMPRODUCT((E173:E234={"Permanent","Perm Risk"})*O173:O234)

Regards

Proably not drastic in with the small range in use here, but in large volumes of data, that version would cause a significant increase in calculation times.

 

[bosco_yip]

Proably not drastic in with the small range in use here, but in large volumes of data, that version would cause a significant increase in calculation times.

If consider performance, I think SUMIF+SUMIF will faster than SUM+SUMIF+{array constant}

=SUMIF(E173:E234,"Perm Risk",O173:O234)+SUMIF(E173:E234,"Permanent",O173:O234)

Regards