Sumproduct - two sumifs - help reqd

[Mongy]

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

{=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

 

[acw]

Hi

Try

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

Do not array enter.

Tony

 

[pgc01]

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

=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)

=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]

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]

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

{=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]

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.