+ Reply to Thread
Results 1 to 11 of 11

Can't Figure out How (If?) to use Vlookups with CountIfs

  1. #1
    Registered User
    Join Date
    09-25-2013
    Location
    Denver, CO
    MS-Off Ver
    Excel 2010
    Posts
    10

    Can't Figure out How (If?) to use Vlookups with CountIfs

    See attached spreadsheet.

    I can't figure out how to move data from the Raw Data tab over to the Market View tab methodically and without having to go into each cell and modify the formula.

    I'm trying to populate the cells in Columns C - F and need to do a countif based on other data including the data in Column B. I've put examples of how I have done it so far in rows 12 - 26 but I have a LOT to fill out and don't want to have to modify the formula manually each time.

    I'm ALSO trying to populate columns L - W and need to do Countifs based on info from the Raw Data tab and some items on the Market View tab. I've pre-populated the data in rows 12 - 26 but have a lot to fill out and don't want to have to modify the formula manually each time.

    If it's possible, I'd like to complete this WITHOUT using any Macros.

    I'm using Excel 2016

    Thank you in advance for any help you can offer!
    Attached Files Attached Files
    Last edited by bullpen; 01-06-2020 at 06:34 PM.

  2. #2
    Valued Forum Contributor
    Join Date
    12-28-2014
    Location
    NSW, Australia
    MS-Off Ver
    MS365
    Posts
    604

    Re: Can't Figure out How (If?) to use Vlookups with CountIfs

    Hi Bullpen, try this in Cell C13 and copy across and down as needed:

    Please Login or Register  to view this content.
    I was just about to look at the L-W question but realised I'm running late for another engagement. I will look into it tomorrow if no-one else has answered it for you... Fingers crossed

  3. #3
    Valued Forum Contributor
    Join Date
    12-28-2014
    Location
    NSW, Australia
    MS-Off Ver
    MS365
    Posts
    604

    Re: Can't Figure out How (If?) to use Vlookups with CountIfs

    Hi, got home and thought I'd have a look before bed.
    To make it much easier, I continued the numbers in column A down for each row (I hope that's not a problem).
    The first problem I found was that you had L$2 in your formula and it should have been just L2. After that it could be copied across and down to give your expected results but only for rows 13-18.

    I've come up with a solution that I believe does what you want. The formula in cell L13 is:-
    Please Login or Register  to view this content.
    The main 2 changes were:-
    This:- INDEX($L$2:$W$7,ROW(J9)-COUNTIF($B$13:$B13,"Chicago")*8,COLUMN(A1))
    Replaced:- L2

    This makes an index table at the top and automatically keeps cycling through rows 2-7 for each 6-row section you paste the formula into.

    This: INDIRECT("'Raw Data'!G"&COUNTIF($B$13:$B13,"Chicago")+5&":BO"&COUNTIF($B$13:$B13,"Chicago")+5)
    replaced:- 'Raw Data'!$G$6:$BO$6

    This also counts the number of Chicago's that have appeared in column B up to that point and uses it to access the correct row in the Raw Data sheet.
    Attached Files Attached Files

  4. #4
    Registered User
    Join Date
    09-25-2013
    Location
    Denver, CO
    MS-Off Ver
    Excel 2010
    Posts
    10

    Re: Can't Figure out How (If?) to use Vlookups with CountIfs

    Beamernsw....this is INCREDIBLE!! It's so helpful! Thank you! Thank you! Thank you!

    Any chance you could explain HOW it works?

  5. #5
    Registered User
    Join Date
    09-25-2013
    Location
    Denver, CO
    MS-Off Ver
    Excel 2010
    Posts
    10

    Re: Can't Figure out How (If?) to use Vlookups with CountIfs

    Beamernsw, do you know why the formulas in Columns L - W stop working if I add rows at the top?

  6. #6
    Registered User
    Join Date
    09-25-2013
    Location
    Denver, CO
    MS-Off Ver
    Excel 2010
    Posts
    10

    Re: Can't Figure out How (If?) to use Vlookups with CountIfs

    Beamernsw, is there a way to configure the solution in columns L - W by referencing the data in cell B2 to lookup which row to count on the Raw Data tab instead of using Chicago? I ask because I have to do another spreadsheet that will be similar but may have a different number of markets and Chicago may not be one of the markets.

    Ideally, it would look up Cell B12 for rows 13-18, Lookup Cell B20 for Rows 21 - 26, etc. when determining what row on the Raw Data tab it should do the countifs on when populating columns L - W.

    Does this make sense?

  7. #7
    Valued Forum Contributor
    Join Date
    12-28-2014
    Location
    NSW, Australia
    MS-Off Ver
    MS365
    Posts
    604

    Re: Can't Figure out How (If?) to use Vlookups with CountIfs

    Hi, sure... Sticking to the Absolute cell reference is the easiest, but you'd have to manually change it for each different Infrastructure type. If you're happy to do that then it's less to possibly go wrong in the future.
    If you'd like to keep the same formula all the way down and not have to edit it for each section, then you could add another helper column showing the Infrastructure type on every line, or you could use that table you have at F2:F7 and get it to search for the name in cell F2.

    As for explaining the formulas, I'll start with the one in Cell C13:-
    COUNTIFS(INDIRECT("'Raw Data'!$G$"&MATCH(INDIRECT("B"&MATCH("Chicago",$B8:$B13,0)+ROW(6:6)),'Raw Data'!$B:$B,0)&":$BO$"&MATCH(INDIRECT("B"&MATCH("Chicago",$B8:$B13,0)+ROW(6:6)),'Raw Data'!$B:$B,0)),C$10,'Raw Data'!$G$1:$BO$1,$B13,'Raw Data'!$G$3:$BO$3,$B$9)

    The Green section looks for "Chicago" in B8:B13 and returns the line number then adds 6 (6+6). The next line down (cell C14) would search for "Chicago" in B9:B14 and add 7 (5+7). Answer will always be 12 for this group.
    The Red section searches for a match of the contents of B12 ("B"&result of green search - 12) which is "Utility Feeds". It searches for this within the column 'Raw Data'!$B:$B.
    It finds "Utility Feeds" in row 6 of Raw Data. Now that entire red and green section have finished and returned that number 6. And it does the exact same thing again after the ":$BO$" section.

    So if we type that formula out but manually replace the coloured bits with the 6, we have:
    COUNTIFS(INDIRECT("'Raw Data'!$G$"&6&":$BO$"&6),C$10,'Raw Data'!$G$1:$BO$1,$B13,'Raw Data'!$G$3:$BO$3,$B$9)

    And we can now also remove the INDIRECT function since we know the row number to search:
    COUNTIFS('Raw Data'!$G$6:$BO$6,C$10,'Raw Data'!$G$1:$BO$1,$B13,'Raw Data'!$G$3:$BO$3,$B$9)


    Edit:-
    So that means if you wish to use the Absolute cell references (easiest), then replace:-
    INDIRECT("B"&MATCH("Chicago",$B8:$B13,0)+ROW(6:6))

    with:-
    $B$12 (both times in the formula)
    Last edited by Beamernsw; 01-07-2020 at 05:13 AM.

  8. #8
    Valued Forum Contributor
    Join Date
    12-28-2014
    Location
    NSW, Australia
    MS-Off Ver
    MS365
    Posts
    604

    Re: Can't Figure out How (If?) to use Vlookups with CountIfs

    I was about to post an explanation of the formula in Cell L13, but I realised I need to head out tonight and don't have time at the moment. It kind of works on the same principles as the last formula though.
    The section "COUNTIF($B$13:$B13,"Chicago")+5" counts how many times "Chicago" appears between B13 and the current cell. The first B13 is locked in place but the second isn't. So as the formula is copied down it increases from B13:B13 to B13:B74 for example.
    Later in the formula it is multiplied by 8 (*8) that is because there are 8 lines from one "Chicago" to the next.

    I hope that helps, I can explain it better tomorrow if need be.

    Hopefully, that wasn't too much info in one hit

  9. #9
    Registered User
    Join Date
    09-25-2013
    Location
    Denver, CO
    MS-Off Ver
    Excel 2010
    Posts
    10

    Re: Can't Figure out How (If?) to use Vlookups with CountIfs

    Thank you Beamernsw! That helped a lot for columns C - F!

    I can't figure out how to modify the formulas in columns L - W so that it uses B12 as it's lookup reference on the Raw Data tab to find the right row to count. I also don't know how to modify so that it doesn't need to include Chicago in the formula as I will be doing this again for other markets where Chicago will not be part of.

    Here is the formula in cell L13:
    =IF(L$11<=$A12,"", IF(COUNTIFS(INDIRECT("'Raw Data'!G"&COUNTIF($B$13:$B13,"Chicago")+5&":BO"&COUNTIF($B$13:$B13,"Chicago")+5),L$11,'Raw Data'!$G$2:$BO$2,INDEX($L$2:$W$7,ROW(J9)-COUNTIF($B$13:$B13,"Chicago")*8,COLUMN(A1)))=0,"",CONCATENATE(INDEX($L$2:$W$7,ROW(J9)-COUNTIF($B$13:$B13,"Chicago")*8,COLUMN(A1))," (",COUNTIFS(INDIRECT("'Raw Data'!G"&COUNTIF($B$13:$B13,"Chicago")+5&":BO"&COUNTIF($B$13:$B13,"Chicago")+5),L$11,'Raw Data'!$G$2:$BO$2,INDEX($L$2:$W$7,ROW(J9)-COUNTIF($B$13:$B13,"Chicago")*8,COLUMN(A1))),")")))

    Thank you for all of the time you've spent helping me get this figured out!

  10. #10
    Registered User
    Join Date
    09-25-2013
    Location
    Denver, CO
    MS-Off Ver
    Excel 2010
    Posts
    10

    Re: Can't Figure out How (If?) to use Vlookups with CountIfs

    Beamernsw, I was finally able to figure out how to do it in such a way that allows me to be flexible with other markets. I changed the formula in cell L13 to:

    =IF(L$11<=$A12,"",IF(COUNTIFS(INDIRECT("'Raw Data'!$G$"&MATCH($B12,'Raw Data'!$B:$B,0)&":$BO$"&MATCH($B12,'Raw Data'!$B:$B,0)),L$11,'Raw Data'!$G$2:$BO$2,L$2)=0,"",CONCATENATE(L$2," (",COUNTIFS(INDIRECT("'Raw Data'!$G$"&MATCH($B12,'Raw Data'!$B:$B,0)&":$BO$"&MATCH($B12,'Raw Data'!$B:$B,0)),L$11,'Raw Data'!$G$2:$BO$2,L$2),")")))

    I had to update some of the values when I copied it down to L18 but then I could move them over and then copy the entire "table" and paste on all of the rows below.

    Thank you again for all of your help. You have no idea how much time you saved me.

  11. #11
    Valued Forum Contributor
    Join Date
    12-28-2014
    Location
    NSW, Australia
    MS-Off Ver
    MS365
    Posts
    604

    Re: Can't Figure out How (If?) to use Vlookups with CountIfs

    Hi Bullpen, glad I could help. I had a quick look at your modified formula and I'm pretty sure that's exactly how I would've done it. Well done

+ 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. Replies: 1
    Last Post: 06-22-2017, 02:58 AM
  2. Please help, I can't figure out why countifs( isn't working
    By Blackhawks in forum Excel Formulas & Functions
    Replies: 8
    Last Post: 02-10-2014, 09:56 PM
  3. Replies: 4
    Last Post: 02-03-2014, 08:05 AM
  4. [SOLVED] Unsure how to fill my Rows with correct COUNTIFS and vlookups
    By dmejia174 in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 07-24-2013, 05:51 AM
  5. Replies: 0
    Last Post: 12-16-2011, 09:01 AM
  6. Calculating monthly sales figure required to make annual turnover figure
    By CatIsoSio Sky in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 07-27-2010, 04:42 PM
  7. Dynamic Named Range, COUNTIFs & VLookups
    By pmd in forum Excel General
    Replies: 7
    Last Post: 05-31-2010, 08:57 AM

Tags for this Thread

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