+ Reply to Thread
Results 1 to 7 of 7

Multiplying COUNTIFS using a VLOOKUP table rather than making lots of COUNTIFS

  1. #1
    Registered User
    Join Date
    09-04-2018
    Location
    UK
    MS-Off Ver
    2013
    Posts
    24

    Multiplying COUNTIFS using a VLOOKUP table rather than making lots of COUNTIFS

    I've made this basic spreadsheet attached. In the ‘Workers’ sheet it COUNTIFS every time that worker’s name appears in the ‘Session’ column of the ‘Data’ spreadsheet, takes the ‘Score’ and using the info in the ‘Table’ spreadsheet gives a ‘Total Cost’ figure of every session associated with that worker.

    What I want to know is, is there a way of doing this same calculation without having to write out a new ‘+COUNTIFS’ function for every single ‘Score’ to ‘Cost’ calculation, as you can see I’ve done in Workers!B2 and down. I’ve been trying to turn it into a VLOOKUP formula to simplify/shorten it but haven’t been able to work out how to do that.

    Thanks,
    Attached Files Attached Files
    Last edited by DIAL90; 03-19-2019 at 03:48 PM.

  2. #2
    Forum Expert 63falcondude's Avatar
    Join Date
    08-22-2016
    Location
    USA
    MS-Off Ver
    365
    Posts
    6,266

    Re: Multiplying COUNTIFS using a VLOOKUP table rather than making lots of COUNTIFS

    There is no attachment.

    To upload an Excel workbook, follow these steps:
    1) Click on "Go Advanced"
    2) Click on "Manage Attachments"
    3) Click on "Choose File"
    4) Choose your file and click on "Open"
    5) Click on "Upload"
    6) Click on "Close this window"

  3. #3
    Registered User
    Join Date
    09-04-2018
    Location
    UK
    MS-Off Ver
    2013
    Posts
    24

    Re: Multiplying COUNTIFS using a VLOOKUP table rather than making lots of COUNTIFS

    Added the attachment now, sorry about that!

  4. #4
    Forum Guru Bo_Ry's Avatar
    Join Date
    09-10-2018
    Location
    Thailand
    MS-Off Ver
    MS 365
    Posts
    7,211

    Re: Multiplying COUNTIFS using a VLOOKUP table rather than making lots of COUNTIFS

    Please try at B2 and drag down
    =SUMPRODUCT(LOOKUP(ISNUMBER(SEARCH(A2,Data!$B$2:$B$17))*Data!$C$2:$C$17,N(+Table!$A$1:$B$10)))

  5. #5
    Registered User
    Join Date
    09-04-2018
    Location
    UK
    MS-Off Ver
    2013
    Posts
    24

    Re: Multiplying COUNTIFS using a VLOOKUP table rather than making lots of COUNTIFS

    This works great thanks!

  6. #6
    Registered User
    Join Date
    09-04-2018
    Location
    UK
    MS-Off Ver
    2013
    Posts
    24

    Re: Multiplying COUNTIFS using a VLOOKUP table rather than making lots of COUNTIFS

    To follow up. Is it possible to add criteria to the formula in Workers!B2 so that it only calculates the rows in Data!A Column that come between the dates in Workers!D1 & E1?

    Thanks.
    Attached Files Attached Files

  7. #7
    Forum Guru Bo_Ry's Avatar
    Join Date
    09-10-2018
    Location
    Thailand
    MS-Off Ver
    MS 365
    Posts
    7,211

    Re: Multiplying COUNTIFS using a VLOOKUP table rather than making lots of COUNTIFS

    Please try at B2
    =SUMPRODUCT(LOOKUP(ISNUMBER(SEARCH($A2,Data!$B$2:$B$17))*Data!$C$2:$C$17*(Data!$A$2:$A$17>=$D$1)*(Data!$A$2:$A$17<=$E$1),N(+Table!$A$1:$B$10)))

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. Making a pivot table with lots of data
    By meganed5 in forum Excel Charting & Pivots
    Replies: 1
    Last Post: 08-02-2017, 12:44 PM
  2. [SOLVED] Sumproduct to replace countifs as countifs don't work on external source reference
    By KrishnaSagar in forum Excel Formulas & Functions
    Replies: 5
    Last Post: 04-02-2017, 02:33 AM
  3. How can I run CountIfs + CountIfs without double counting?
    By hiitsjessie in forum Excel General
    Replies: 1
    Last Post: 02-06-2017, 04:49 PM
  4. Making CountIFS and SUMIFS Formula Dynamic
    By HangMan in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 06-30-2015, 04:58 AM
  5. COUNTIFS OR...multiple countifs without duplication in the numbers
    By HooligaD in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 12-21-2012, 09:53 AM
  6. Replies: 0
    Last Post: 12-16-2011, 09:01 AM
  7. Making countifs reference inequalities
    By mremmenga in forum Excel General
    Replies: 1
    Last Post: 10-22-2008, 05:42 PM

Bookmarks

Posting Permissions

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1