+ Reply to Thread
Results 1 to 10 of 10

COUNTIFS not counting all cells

  1. #1
    Registered User
    Join Date
    01-05-2015
    Location
    Glasgow,Scotland
    MS-Off Ver
    2010
    Posts
    2

    COUNTIFS not counting all cells

    Hey,

    So i am struggling to get the result I'm after using the 'countifs' function (a new function to me)

    Essentially i have an excel doc with a list of events and potential visitors (from a drop down) to these events and i need to track which visitors attended which events. eg below

    Event LOCATION V1 V2
    FMTPE Cheshunt Bob
    FMTPE Reading
    FMTPE Newcastle Bob Sue
    FMTPE Bristol Sue Bob
    BTI Marlow
    JTD Cheshunt
    JTD Reading
    JTD
    JTD Bristol


    I can largely achieve this with a pivot table, however on occasion there will be more than 1 visitor which i need to account for and total regardless of which column they appear in.

    So after searching on here, i decided to abandon the pivot table and utilise the countifs function.

    I'm aiming to achieve something with an output similar to below:

    Visitor FMTPE BTI
    Bob 3 0
    Sue 2 0


    However whenever i run the countifs function it only seems to count the Visitor 1 column.

    I have attached an example document, but essentially i am asking it to count if it matches the event name and the name of the person.

    I am using office 2010
    Hope someone can correct my most likely obvious error.

    Thanks in advance

    JD
    Attached Files Attached Files

  2. #2
    Forum Expert daffodil11's Avatar
    Join Date
    07-11-2013
    Location
    Phoenixville, PA
    MS-Off Ver
    MS Office 2016
    Posts
    4,465

    Re: COUNTIFS not counting all cells

    I didn't see an attachment, but we can pretend.

    Event = A1

    Visitor = A11

    Since you have 2 visitor columns, you're essentially asking for an "OR" operator. You can do this with COUNTIFS+COUNTIFS or just use SUMPRODUCT.

    B12:
    Please Login or Register  to view this content.
    or

    Please Login or Register  to view this content.
    for both, then copy right and down
    Make Mom proud: Add to my reputation if I helped out!

    Make the Moderators happy: Mark the Thread as Solved if your question was answered!

  3. #3
    Forum Expert martindwilson's Avatar
    Join Date
    06-23-2007
    Location
    London,England
    MS-Off Ver
    office 97 ,2007
    Posts
    19,320

    Re: COUNTIFS not counting all cells

    formula in g2 filled down


    A
    B
    C
    D
    E
    F
    G
    H
    1
    Event LOCATION V1 V2 Visitor FMTPE
    2
    FMTPE Cheshunt Bob Bob
    3
    =SUMPRODUCT(($A$2:$A$10=$G$1)*($C$2:$D$10=F2))
    3
    FMTPE Reading Sue
    2
    =SUMPRODUCT(($A$2:$A$10=$G$1)*($C$2:$D$10=F3))
    4
    FMTPE Newcastle Bob Sue
    5
    FMTPE Bristol Sue Bob
    6
    BTI Marlow
    7
    JTD Cheshunt
    8
    JTD Reading
    9
    JTD
    10
    JTD Bristol
    "Unless otherwise stated all my comments are directed at OP"

    Mojito connoisseur and now happily retired
    where does code go ?
    look here
    how to insert code

    how to enter array formula

    why use -- in sumproduct
    recommended reading
    wiki Mojito

    how to say no convincingly

    most important thing you need
    Martin Wilson: SPV
    and RSMBC

  4. #4
    Forum Guru
    Join Date
    08-05-2004
    Location
    NJ
    MS-Off Ver
    365
    Posts
    13,578

    Re: COUNTIFS not counting all cells

    Going to throw another new Function at you, SUMPRODUCT

    In B3 copied across and down

    =SUMPRODUCT(('Master List'!$D$3:$D$189=$A3)+('Master List'!$E$3:$E$189=$A3))*('Master List'!$A$3:$A$189=B$2)
    Does that work for you?
    ChemistB
    My 2?

    substitute commas with semi-colons if your region settings requires
    Don't forget to mark threads as "Solved" (Edit First post>Advanced>Change Prefix)
    If I helped, Don't forget to add to my reputation (click on the little star at bottom of this post)

    Forum Rules: How to use code tags, mark a thread solved, and keep yourself out of trouble

  5. #5
    Forum Guru Pete_UK's Avatar
    Join Date
    12-31-2011
    Location
    Warrington, England
    MS-Off Ver
    Office 2019 (still learning)
    Posts
    24,604

    Re: COUNTIFS not counting all cells

    I can't see an attachment (the FAQ describes how to attach a file to one of your posts).

    Pete

  6. #6
    Forum Guru TMS's Avatar
    Join Date
    07-15-2010
    Location
    The Great City of Manchester, NW England ;-)
    MS-Off Ver
    MSO 2007,2010,365
    Posts
    44,091

    Re: COUNTIFS not counting all cells

    No workbook, no data and no formula. How would you have us assess the problem?
    Trevor Shuttleworth - Retired Excel/VBA Consultant

    I dream of a better world where chickens can cross the road without having their motives questioned

    'Being unapologetic means never having to say you're sorry' John Cooper Clarke


  7. #7
    Forum Expert mrice's Avatar
    Join Date
    06-22-2004
    Location
    Surrey, England
    MS-Off Ver
    Excel 2013
    Posts
    4,967

    Re: COUNTIFS not counting all cells

    You could try

    =COUNTIFS(A:A,I$1,C:C,$H2)+ COUNTIFS(A:A,I$1,D:D,$H2)

    pasted into I2 where Visitor is in column H and events in columns I onwards.
    Martin

  8. #8
    Registered User
    Join Date
    01-05-2015
    Location
    Glasgow,Scotland
    MS-Off Ver
    2010
    Posts
    2

    Re: COUNTIFS not counting all cells

    Countifs demo.xlsx

    This was the attachment, also seem to have posted this 3 times, apologies.

    Once i work out how to delete the other 2 i will!

    Thanks for the tips will try tomorrow when im properly able to play around.

  9. #9
    Forum Guru TMS's Avatar
    Join Date
    07-15-2010
    Location
    The Great City of Manchester, NW England ;-)
    MS-Off Ver
    MSO 2007,2010,365
    Posts
    44,091

    Re: COUNTIFS not counting all cells


    A
    B
    C
    D
    E
    1
    Event
    LOCATION
    V1
    V2*
    2
    FMTPE
    Cheshunt
    Bob
    3
    FMTPE
    Reading
    4
    FMTPE
    Newcastle
    Bob
    Sue
    5
    FMTPE
    Bristol
    Sue
    Bob
    6
    BTI
    Marlow
    7
    JTD
    Cheshunt
    8
    JTD
    Reading
    9
    JTD
    10
    JTD
    Bristol
    11
    12
    13
    14
    15
    16
    Visitor
    FMTPE
    BTI
    17
    Bob
    3
    0
    =COUNTIFS($A$2:$A$10,B$16,$C$2:$C$10,$A17)+COUNTIFS($A$2:$A$10,B$16,$D$2:$D$10,$A17)
    18
    Sue
    2
    0

  10. #10
    Forum Expert daddylonglegs's Avatar
    Join Date
    01-14-2006
    Location
    England
    MS-Off Ver
    2016
    Posts
    14,675

    Re: COUNTIFS not counting all cells

    Quote Originally Posted by Doppelganger View Post
    Attachment 368451.....also seem to have posted this 3 times, apologies..
    I have "merged" all three threads, with all replies....and tidied up a little....
    Audere est facere

+ 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. Counting non-duplicates using countifs() with other criteria - how?
    By tforan in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 11-19-2014, 03:31 AM
  2. [SOLVED] COUNTIFS is double counting
    By infiniticihr in forum Excel Formulas & Functions
    Replies: 6
    Last Post: 08-22-2014, 01:09 PM
  3. Countifs and Double Counting
    By jlacsina in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 12-18-2013, 09:55 PM
  4. COUNTIFS not counting all numbers in one cell
    By taichi56 in forum Excel Formulas & Functions
    Replies: 12
    Last Post: 07-16-2013, 05:46 PM
  5. Countifs not counting, returns #value
    By Qualo_Jinn in forum Excel General
    Replies: 3
    Last Post: 12-09-2011, 04:13 PM

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