Find correspondend Amount by Period and Country via SumProduct formula

[kelvin-lkh]

Dear All,

I use the sumproduct formula to lookup the value from table at B10:G22 with reference to criteria on C2 and Row 3.

The formula works well if the value appear in D11:G22.

However, it becomes an issue if some cell in D11:G22 showing #VALUE! even the they are not for the look up period.

the table will show #VALUE! is because I retrieve the value from Hyperion with Actual scenario. so those month which are not yet actualised wont' have any amount hence showing #VALUE!.

Is there any way I can resolve this issue?

Appreciate your advice in advance.

 

[Khalid NGO]

Hi Kelvin,

Try this: {array formula}

=SUM((C$3=$D$10:$G$10)*($C$2=$B$11:$B$22)*IFERROR($D$11:$G$22,0))

{array formula needs to be entered with
a key combination of Ctrl+Shift+Enter}

Regards,

 

[bosco_yip]

Or………..

In C4, non-CSE formula copy across :

=IFERROR(INDEX($E$11:$G$22,MATCH($C$2,$B$11:$B$22,0),MATCH(C$3,$E$10:$G$10,0)),"")

Regards

 

[Deepak]

Few more simple formula...

=IFERROR(VLOOKUP($C$2,$B$10:$G$22,MATCH(C$3,$B$10:$G$10,0)),0)

=IFERROR(SUMIF(B10:$B$22,$C$2,INDEX($B$10:$G$22,,MATCH(C$3,$B$10:$G$10,0))),0)

 

[kelvin-lkh]

Thanks All!!

Khalid's solution is best fit for me incase there are two column with same country name.