Excel SumProduct using CountA

Posted on 2012-08-21
Last Modified: 2012-08-29
What is the correct way to use CountA for a dynamic row in a SumProduct?

This works fine;


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
Question by:newparadigmz
    LVL 24

    Expert Comment

    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.
    LVL 50

    Expert Comment

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


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

    regards, barry
    LVL 1

    Expert Comment

    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.


    Author Comment

    @ 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"
    LVL 50

    Accepted Solution

    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


    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:$G,COUNTA(Data!$G:$G))
    Range2 as =Data!$A$2:INDEX(Data!$A:$A,COUNTA(Data!$G:$G))


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

    then you get just


    regards, barry

    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    How your wiki can always stay up-to-date

    Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
    - Increase transparency
    - Onboard new hires faster
    - Access from mobile/offline

    What is a Form List Box? (skip if you know this) The forms List Box is the alternative to the ActiveX list box. If you are using excel 2007, you first make sure you have a developer tab (click the Orb)->"Excel Options"->Popular->"Show Developer tab…
    Convert between Excel file formats (.XLS, .XLSX, .XLSM) with/without macro option David Miller (dlmille) Intro Over this past Fall, I've had the opportunity to see several similar requests and have developed a couple related solutions associate…
    The view will learn how to download and install SIMTOOLS and FORMLIST into Excel, how to use SIMTOOLS to generate a Monte Carlo simulation of 30 sales calls, and how to calculate the conditional probability based on the results of the Monte Carlo …
    Excel styles will make formatting consistent and let you apply and change formatting faster. In this tutorial, you'll learn how to use Excel's built-in styles, how to modify styles, and how to create your own. You'll also learn how to use your custo…

    760 members asked questions and received personalized solutions in the past 7 days.

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

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    8 Experts available now in Live!

    Get 1:1 Help Now