**Offset**function:

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

Solved

Posted on 2012-08-21

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

This works fine;

=SUMPRODUCT((Data!$I$2:$IJ

but this gives me a #VALUE error;

=SUMPRODUCT(("Pnl!$I$2:$IJ

even though my CountA=66

5 Comments

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

=SUMPRODUCT((Data!$I$1:$IJ

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

regards, barry

This greatly simplifies formulas.

http://support.microsoft.c

Regards.

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

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

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

Headers as =Data!$I$1:$IJ$1

Range1 as =Data!$G$2:INDEX(Data!$G:$

Range2 as =Data!$A$2:INDEX(Data!$A:$

and

Range3 as =Data!$I$2:INDEX(Data!$IJ:

then you get just

=SUMPRODUCT((Headers=Sumar

regards, barry

Dynamic-range-example.xlsx

By clicking you are agreeing to Experts Exchange's Terms of Use.

Title | # Comments | Views | Activity |
---|---|---|---|

Excel 2010 - Select category per Max values v.2 | 2 | 17 | |

Copy paste as values | 4 | 33 | |

How to edit the macro code to limit the number of characters when creating worksheet tab name | 5 | 29 | |

Search Box | 13 | 27 |

Join the community of 500,000 technology professionals and ask your questions.

Connect with top rated Experts

**8** Experts available now in Live!