Results 1 to 2 of 2

Thread: Countif Range Based on Row

  1. #1
    Acolyte Joecam's Avatar
    Join Date
    May 2014
    Posts
    34
    Articles
    0
    Excel Version
    365

    Countif Range Based on Row



    Register for a FREE account, and/
    or Log in to avoid these ads!

    I have a table that contains a client name, target performance metrics and actual performance metric over the course of a year. Each client has multiple performance metrics which get monitored and each metric has a different target performance rate.

    I'm trying to create a summary view of how many performance metrics have been met for each client. I'm currently doing this by adding a bunch of countif statements, but having to create so many statements is very time consuming and if I add a client I need to completely recreate the formulas, so I'm looking to upgrade to a formula that will work even if I add new clients or new performance metrics to an existing client.

    What I need is a formula that will count the number of instances where:
    Column A on the Data Driver sheet is equal to Column C on the Client Summary sheet
    & Columns D:AA is greater than or equal to Column C on the Data Driver sheet
    & where the date in Client Summary is equal to the date in Data Driver

    Sorry, I know I'm not explaining this well! Adding a sample spreadsheet to assist.
    Attached Files Attached Files

  2. #2
    Seeker RET's Avatar
    Join Date
    Nov 2020
    Location
    Spain
    Posts
    16
    Articles
    0
    Excel Version
    2019
    Hi @Joecam,

    Try to use:
    =SUMPRODUCT(--($C5='Data Driver'!$A$7:$A$45);--('Data Driver'!D$7$45>='Data Driver'!$C$7:$C$45)) in cell E5 and copy formula to all range of data then

    My Excel is in spanish but i think SUMPRODUCT is OK for en version. Probably you will also need to change ; by ,

    If in doubt, check the attached file
    Attached Files Attached Files

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •