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
 

Some videos you may like

Excel Facts

Quick Sum
Select a range of cells. The total appears in bottom right of Excel screen. Right-click total to add Max, Min, Count, Average.

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,870
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,870
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,201
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.
 

Watch MrExcel Video

Forum statistics

Threads
1,113,795
Messages
5,544,337
Members
410,603
Latest member
rseckler
Top