+ Reply to Thread
Results 1 to 4 of 4

count if and vlookup all in one from various sheets?

  1. 08-23-2012, 11:57 AM #1
    Lorna B
    Lorna B is offline
    Forum Contributor
    Join Date
    08-06-2009
    Location
    Belfast
    MS-Off Ver
    Excel 2003
    Posts
    107

    count if and vlookup all in one from various sheets?

    Hi guys - Im wondering can someone help me. I usually try everything before I try and get some help so that I can learn what im doing. However due to time constraints at the minute i have tried what i know and it doesnt work - so im reaching out!!!!!


    To explain in context - Im going through our phone contracts at the minute and seeing my last 3 months usage.

    Different networks called etc.

    I want to be able to have a formulae which says. Count column B on all the sheets (may, june. july) if the cell in column A on all the sheets matches column C on the Summary Sheet

    I have obviously taken out more of the information on the tabs however please note that they will not ness be in the same order on all the tabs or even on the same lines or i would have done a simple add, add add etc.

    im thinking its a cross between a countif and vlookup.


    Am I making sense??

    Cheers guys
    Attached Files Attached Files
    Reply With QuoteRegister To Reply
  2. 08-23-2012, 12:16 PM #2
    NBVC
    NBVC is offline
    Forum Expert NBVC's Avatar
    Join Date
    12-06-2006
    Location
    Mississauga, CANADA
    MS-Off Ver
    2003:2010
    Posts
    34,898

    Re: count if and vlookup all in one from various sheets?

    Create a list somewhere else in the sheet of all the sheetnames.

    Say you put that list in K2:K4.. then try:

    =SUMPRODUCT(SUMIF(INDIRECT("'"&$K$2:$K$4&"'!A:A"),$C9,INDIRECT("'"&$K$2:$K$4&"'!B:B")))

    adjusting the $K$2:$K$4 to suit.

    then copy down.
    Where there is a will there are many ways.

    If you are happy with the results, please add to the contributor's reputation by clicking the reputation icon (star icon) below left corner

    Please also mark the thread as Solved once it is solved. Check the FAQ's to see how.
    Reply With QuoteRegister To Reply
  3. 08-24-2012, 06:36 AM #3
    Lorna B
    Lorna B is offline
    Forum Contributor
    Join Date
    08-06-2009
    Location
    Belfast
    MS-Off Ver
    Excel 2003
    Posts
    107

    Re: count if and vlookup all in one from various sheets?

    Hi - I have tried this but its says #REF!

    not too sure what i have done.

    Please see attached workbook.

    Thanks

    Lorna
    Attached Files Attached Files
    Last edited by Cutter; 08-24-2012 at 12:33 PM. Reason: Removed whole post quote
    Reply With QuoteRegister To Reply
  4. 08-24-2012, 07:52 AM #4
    NBVC
    NBVC is offline
    Forum Expert NBVC's Avatar
    Join Date
    12-06-2006
    Location
    Mississauga, CANADA
    MS-Off Ver
    2003:2010
    Posts
    34,898

    Re: count if and vlookup all in one from various sheets?

    Your "June" in K3 has a trailing space in it. Remove it and voila'

    Also, you will have to update the B:B reference in each column to match corresponding column you want to sum in the monthly sheets.
    Reply With QuoteRegister To Reply
+ Reply to Thread
« Previous Thread | Next Thread »

Thread Information

Users Browsing this Thread

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

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

Forum Rules


Search Engine Friendly URLs by vBSEO 3.6.0 RC 1