Weird things happening to Excel lately; now SUMPRODUCT isn't working properly

[DashboardNovice]

In the attached file I have a SUMPRODUCT on the tab called 'Data Sources for Charts.' I am working in cell AC5.

For some reason this isn't working.

 

[Colin Legg]

Your formula in AC5 is
=SUMPRODUCT(($J$6:$J$100=AB5)*($N$6:$N$47=AC4))

I'm not quite sure what that formula is meant to be calculating. Was it meant to be
=SUMPRODUCT(($J$6:$J$100=$AB10)*($I$6:$I$100=AC$4))

If yes, then you could use COUNTIFS() which would be faster or you could even consider using a pivot table. I think a pivot table would be an extremely good option for this one.

Hope that helps...

 

[NARAYANK991]

Hi ,

The SUMPRODUCT function operates on arrays , and the first requirement is that the arrays be of the same size ; so having the first array ranging from row 6 through row 100 , while the second array ranges from row 6 through row 47 will not work.

Narayan

 

[DashboardNovice]

Your formula in AC5 is
=SUMPRODUCT(($J$6:$J$100=AB5)*($N$6:$N$47=AC4))

I'm not quite sure what that formula is meant to be calculating. Was it meant to be
=SUMPRODUCT(($J$6:$J$100=$AB10)*($I$6:$I$100=AC$4))

If yes, then you could use COUNTIFS() which would be faster or you could even consider using a pivot table. I think a pivot table would be an extremely good option for this one.

Hope that helps...

Thank you sir. You got me to see my mistake. I was referencing two arrays in two tables instead of two arrays inside the same table.

 

[DashboardNovice]

Hi ,

The SUMPRODUCT function operates on arrays , and the first requirement is that the arrays be of the same size ; so having the first array ranging from row 6 through row 100 , while the second array ranges from row 6 through row 47 will not work.

Narayan

Thank you for this tip.