• Status: Solved
• Priority: Medium
• Security: Public
• Views: 496

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
0
1 Solution

Commented:
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.
0

Commented:
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
0

Commented:
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.
0

Author Commented:
@ 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"
0

Commented:
Yes, works for me, see attached

The formula in D7 copied across and down is the same as suggested above, then the versions in D14:F16 are the same. Highlighted cells E7 and E14 pick up the highlighted values from the Data sheet, so D14 is this version

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

You may also want to check out the dynamic ranges suggested by others here - you can use effectively the same formula but define

Range1 as =Data!\$G\$2:INDEX(Data!\$G:\$G,COUNTA(Data!\$G:\$G))
Range2 as =Data!\$A\$2:INDEX(Data!\$A:\$A,COUNTA(Data!\$G:\$G))

and

Range3 as =Data!\$I\$2:INDEX(Data!\$IJ:\$IJ,COUNTA(Data!\$G:\$G))

then you get just

regards, barry
Dynamic-range-example.xlsx
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.