asked on

Excel SumProduct using CountA

What is the correct way to use CountA for a dynamic row in a SumProduct?

This works fine;

=SUMPRODUCT((Data!$I$2:$IJ$66)*(Data!$I$1:$IJ$1=Sumary!D$1)*(Data!$G$2:$G$66=Sumary!$A7)*(Data!$A$2:$A$66="D"))

but this gives me a #VALUE error;

=SUMPRODUCT(("Pnl!$I$2:$IJ$" & COUNTA(Data!$G:$G))*(Data!$I$1:$IJ$1=Sumary!D$1)*("Pnl!$G$2:$G$" & COUNTA(Data!$G:$G)=Sumary!$A6)*("Pnl!$A$2:$A$" & COUNTA(Data!$G:$G)="D"))

even though my CountA=66

Steve

I think you would need to use the Offset function:

Which would be easier if you set dynamic named ranges first, then used those in the formula.

barry houdini

Try putting the COUNTA formula in a cell, e.g. Z2 and then use this version

=SUMPRODUCT((Data!$I$1:$IJ$1=Sumary!D$1)*(Data!$G$2:INDEX(Data!$G:$G,$Z$2)=Sumary!$A7)*(Data!$A$2:INDEX(Data!$A:$A,$Z$2)="D"),Data!$I$2:INDEX(Data!$IJ:$IJ,$Z$2))

If you don't want a reference cell just put COUNTA(Data!$G:$G) in place of the three $Z$2s

regards, barry

nombplum

I agree with The_Barman. I frequently used dynamic named ranges for such tasks. See the following link explaining how do define these dynamic ranges from the Microsoft website.

This greatly simplifies formulas.

http://support.microsoft.com/kb/830287

Regards.

newparadigmz

ASKER

@ barry, this didn't work for me. does it work for you?

"If you don't want a reference cell just put COUNTA(Data!$G:$G) in place of the three $Z$2s"

ASKER CERTIFIED SOLUTION

barry houdini

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial