Sum based on criteria from another sheet

[Wolfgang17]

Looking for help with summing based on criteria. Right now I have this which is summing based on data from a data sheet. It works great.

=SUMPRODUCT(SUMIF(INDIRECT("N5:N47"),CHOOSE({1,2,3},Data!$P$3,Data!$P$4,Data!$P$5),INDIRECT("M5:M47")))+SUM($H$5:$H$47)

However I would like to add another column, and Sum the above only if it meets the Criteria found in column B. The criteria is a code number 712 from the data sheet.

Would a nested if statement work? If so, how would that look?

Any help would be appreciated.

 

[Robert Mika]

try:
=SUMPRODUCT(SUMIFS(INDIRECT("M5:M47"),INDIRECT("N5:N47"),CHOOSE({1,2,3},Data!$P$3,Data!$P$4,Data!$P$5),INDIRECT("b5:b47"),712))+SUM($H$5:$H$47)
For Excel 07/10/13

 

[Wolfgang17]

Unfortunately it didn't work. It still sums all data regardless of the 712 in column B.

 

[Robert Mika]

After revision of your formula I think thi sis what you after:
Data

*

B

C

D

E

F

G

H

I

M

N

O

P

1

*

*

*

*

*

*

*

*

*

*

*

*

2

*

*

169

*

*

*

*

*

*

*

*

*

3

*

*

*

*

*

*

*

*

*

*

*

1

4

*

*

*

*

*

*

*

*

*

*

*

2

5

*

*

*

*

*

*

*

*

*

*

*

3

6

*

*

*

*

*

*

*

*

*

*

*

*

7

*

*

*

*

*

*

*

*

*

*

*

*

8

712

*

*

*

*

*

*

*

100

1

*

*

9

*

*

*

*

*

*

*

*

36

2

*

*

10

712

*

*

*

*

*

*

*

69

3

*

*

11

*

*

*

*

*

*

*

*

*

*

*

*

12

*

*

*

*

*

*

*

*

*

*

*

*

13

*

*

*

*

*

*

*

*

*

*

*

*

14

*

*

*

*

*

*

*

*

*

*

*

*

<colgroup><col style="font-weight:bold; width:30px; "><col style="width:64px;"><col style="width:64px;"><col style="width:64px;"><col style="width:64px;"><col style="width:64px;"><col style="width:64px;"><col style="width:64px;"><col style="width:26px;"><col style="width:64px;"><col style="width:40px;"><col style="width:41px;"><col style="width:64px;"></colgroup><tbody>
</tbody>

Spreadsheet Formulas

Cell Formula D2 =SUMPRODUCT(SUMIFS(M5:M47,N5:N47,list,B5:B47,712))+SUM($H$5:$H$47) <tbody> </tbody>

<tbody>
</tbody>

Excel tables to the web >> Excel Jeanie HTML 4

The list is a Named Range( it does not matter where in the worksheet it is)
(i did not include the SUM of H column)

 

[Wolfgang17]

Thanks for your help. Got it working with the following.
=SUMPRODUCT(SUMIF(INDIRECT("N5:N47"),CHOOSE({1,2,3},Data!$P$3,Data!$P$4,Data!$P$5),INDIRECT("M5:M47")))+SUMIF(B5:B47,Data!J4,H5:H47)

 

[Robert Mika]

Thanks for your help. Got it working with the following.
=SUMPRODUCT(SUMIF(INDIRECT("N5:N47"),CHOOSE({1,2,3},Data!$P$3,Data!$P$4,Data!$P$5),INDIRECT("M5:M47")))+SUMIF(B5:B47,Data!J4,H5:H47)

if that works for you.
For couriosity,What's in range N and M?

 

[Wolfgang17]

if that works for you.
For couriosity,What's in range N and M?

The spreadsheet is a time sheet. Column N is hours worked, and column N is for payroll codes under Header (Other Pay) i.e. 101-Mandated Education,100-Safety Training etc.

Thanks again for your help!

 

[Robert Mika]

The spreadsheet is a time sheet. Column N is hours worked, and column N is for payroll codes under Header (Other Pay) i.e. 101-Mandated Education,100-Safety Training etc.

Thanks again for your help!

You do not need INDIRECT and CHOOSE:

Excel Workbook
	B	C	D	M	N	O	P
2
3							1
4							8
5							3
6	13	13		1	2
7				3	3
8				2	3
9				8	8
10
data