Sum Product

[lejanco]

Hello,

I am having an issue with returning one value from multiple criteria. Starting line 92-98, I need the Line 9 on YIAH to match the date in Line 1 on Sheet5, Column d YIAH to match line 2 in Sheet 5, and column C to match column A (sheet 5)
=SUMPRODUCT((Sheet5!$1:$1=YIAH!$F$9)*(Sheet5!$2:$2=YIAH!$D92)*(Sheet5!$A$3:$A$10=YIAH!$C92))

This is my current formula but returns a funny message from excel. Thanks!!!

[mikeTRON]

Excel ran out of resources while attempting to calculate one or more formulas. As a result, these formulas cannot be evaluated.

Why is column F an array?
Also why are you using sumproduct instead of an index+match?


When I delete the three columns of formulas, that irritating error goes away.

[FDibbins]

I had to reload with manual calc on, otherwise excel just crashed.

a few pointers...
1. You should avoid full rows or columns in sumproduct, it will slow things down
2. with a table like you have on sheet 2, it is often better to have the dates going down and the categories going across. As the table grows downwards, it will naturally grow downwards

Try this regular formula instead of the SP...
=INDEX(Sheet5!$B$4:$AG$10,MATCH($C92,Sheet5!$A$4:$A$10,0),MATCH(YIAH!E$9&"-"&YIAH!$D92,INDEX(Sheet5!$B$1:$AG$1&"-"&Sheet5!$B$2:$AG$2,0),0))

copied down and across

[lejanco]

Thanks! Actually, I did not create sheet2 (i know better than that!), It works! Thanks!

[FDibbins]

Happy to help and thanks for the feedback