Sumproduct - two sumifs - help reqd

Mongy

Board Regular
Joined
Dec 9, 2002
Messages
51
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

Using Function Arguments with nested formulas
If writing INDEX in Func. Arguments, type MATCH(. Use the mouse to click inside MATCH in the formula bar. Dialog switches to MATCH.

acw

MrExcel MVP
Joined
Feb 13, 2004
Messages
4,814
Hi

Try

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

Do not array enter.

Tony
 

pgc01

MrExcel MVP
Joined
Apr 25, 2006
Messages
19,884
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.

Please try

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
 

pgc01

MrExcel MVP
Joined
Apr 25, 2006
Messages
19,884
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 "--".
 

Aladin Akyurek

MrExcel MVP
Joined
Feb 14, 2002
Messages
85,209
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.
 

Mongy

Board Regular
Joined
Dec 9, 2002
Messages
51
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.

Many thanks for your time.
 

Forum statistics

Threads
1,141,686
Messages
5,707,827
Members
421,531
Latest member
Fish0926

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.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

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

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

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
Top