# Sumproduct - two sumifs - help reqd

#### Mongy

##### Board Regular
I know there is a million examples on the board of how these work, and have tested a number of them, but I can't seem to get it to work on my sheet.

The formula I have at the moment is
Code:
``{=SUMPRODUCT(--(WORK_TRANSACTIONS!B2:B5000=Sheet2!E5),--(WORK_TRANSACTIONS!H2:H5000="OTE"),--(WORK_TRANSACTIONS!G2:G5000))}``

Sheet2 E5 is a date, the data in B2:B5000 are dates
H2:H5000 either has OTE or #N/A from a lookup
G2:G5000 are the values I wish to sum
I am using CSE to enter the formula.

I am getting #N/A as the result in the cell.

Can anyone point out any stupid things I am missing or stuffed up on?

Many thanks

### Excel Facts

To shade all formula cells: Home, Find & Select, Formulas to select all formulas. Then apply a light fill color.
Hi

Try

=SUMPRODUCT(--(WORK_TRANSACTIONS!B2:B5000=Sheet2!E5),--(WORK_TRANSACTIONS!H2:H5000="OTE"),(WORK_TRANSACTIONS!G2:G5000))

Do not array enter.

Tony

Hi Mongy

You cannot do arithmetic operations with errors in the sumproduct.

You have to filter the errors in an array formula.

BTW, if you didn't have the errors you would not need to enter with CSE.

Code:
``=SUM(IF(ISNA(WORK_TRANSACTIONS!H2:H5000)=FALSE,IF(WORK_TRANSACTIONS!B2:B5000=Sheet2!E5, IF(WORK_TRANSACTIONS!H2:H5000="OTE",--(WORK_TRANSACTIONS!G2:G5000)))))``

or (less efficient, because multiplication is much slower than decision)

Code:
``=SUM(IF(ISNA(WORK_TRANSACTIONS!H2:H5000)=FALSE,(WORK_TRANSACTIONS!B2:B5000=Sheet2!E5)*(WORK_TRANSACTIONS!H2:H5000="OTE")*(WORK_TRANSACTIONS!G2:G5000)))``

These are array formulas and so MUST be entered with CTRL+SHIFT+ENTER and not just ENTER.

Hope this helps
PGC

P. S. I left the "--" in your values in WORK_TRANSACTIONS!G2:G5000 because I assumed that you are coercing values to numbers. If WORK_TRANSACTIONS!G2:G5000 are numbers you don't need the "--".

I know there is a million examples on the board of how these work, and have tested a number of them, but I can't seem to get it to work on my sheet.

The formula I have at the moment is
Code:
``{=SUMPRODUCT(--(WORK_TRANSACTIONS!B2:B5000=Sheet2!E5),--(WORK_TRANSACTIONS!H2:H5000="OTE"),--(WORK_TRANSACTIONS!G2:G5000))}``

Sheet2 E5 is a date, the data in B2:B5000 are dates
H2:H5000 either has OTE or #N/A from a lookup
G2:G5000 are the values I wish to sum
I am using CSE to enter the formula.

I am getting #N/A as the result in the cell.

Can anyone point out any stupid things I am missing or stuffed up on?

Many thanks

To avoid trouble with #N/A...

=SUMPRODUCT(--(WORK_TRANSACTIONS!B2:B5000=Sheet2!E5),--ISNUMBER(MATCH(WORK_TRANSACTIONS!H2:H5000,{"OTE"},0)),WORK_TRANSACTIONS!G2:G5000)

Needs just enter.

Thanks for all of your prompt replies.

Have tried the responses, as said, the #N/A causes the problem.

I think from my end, the ISNUMBER is the easiest to understand, but I am sure I will have use for the ISNA later on another calc sheet I'm working on.

Replies
6
Views
109
Replies
7
Views
711
Replies
1
Views
268
Replies
6
Views
297
Replies
2
Views
512

1,219,998
Messages
6,151,378
Members
451,023
Latest member
dwalc

### 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