# Sum based on criteria from another sheet

#### Wolfgang17

##### Board Regular
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.

### Excel Facts

Which Excel functions can ignore hidden rows?
The SUBTOTAL and AGGREGATE functions ignore hidden rows. AGGREGATE can also exclude error cells and more.
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

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

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>

 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)

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)

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?

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.

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.

You do not need INDIRECT and CHOOSE:
Excel Workbook
BCDMNOP
2
31
48
53
6131312
733
823
988
10
data

Replies
1
Views
87
Replies
1
Views
312
Replies
5
Views
298
Replies
6
Views
404
Replies
16
Views
750

1,196,328
Messages
6,014,676
Members
441,835
Latest member
rthomas268

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back