+ Reply to Thread
Results 1 to 7 of 7

Count in multiple columns if one condition is met in a different column

  1. #1
    Registered User
    Join Date
    10-18-2012
    Location
    Chattanooga
    MS-Off Ver
    Excel 2007
    Posts
    6

    Count in multiple columns if one condition is met in a different column

    Need to count in Q2:Q65, R2:R65 and T2:T65 if the name in N2:N65 = "daniel"


    Here is the formula I've used trying to test...

    =COUNTIFS(Q2:Q65,"<>",R2:R65,"<>",T2:T65,"<>",N2:N65,"daniel")

    Based on this data...

    1 N Q R S T U V
    2 daniel 1.00 1.00 1.00 1.00 1.00
    3
    4


    But the result = 1 and I need it to be 3 since there is text entered in Q2, R2 and T2

    Where am I missing it?

  2. #2
    Valued Forum Contributor
    Join Date
    04-24-2020
    Location
    Woodbridge, VA
    MS-Off Ver
    2016
    Posts
    434

    Re: Count in multiple columns if one condition is met in a different column

    Since you have text entered into Q, R and T you can try this:

    =IF(N2="daniel",COUNTA(Q2,R2,T2),"") then just copy down

    COUNTA looks for cells that are not empty

  3. #3
    Registered User
    Join Date
    10-18-2012
    Location
    Chattanooga
    MS-Off Ver
    Excel 2007
    Posts
    6

    Re: Count in multiple columns if one condition is met in a different column

    THAT WORKS FOR ONE ROW BUT I NEED IT TO COUNT THE RANGE (N2:N65, etc). ROW 3 MAY BE FOR "TOM" THEN ROW 4 FOR "DANIEL" AGAIN.

  4. #4
    Valued Forum Contributor
    Join Date
    04-24-2020
    Location
    Woodbridge, VA
    MS-Off Ver
    2016
    Posts
    434

    Re: Count in multiple columns if one condition is met in a different column

    Can you attach your file? Do you have a criteria on who the name should be (Tom or Daniel, etc.)?

  5. #5
    Forum Expert
    Join Date
    02-10-2019
    Location
    Georgia, USA
    MS-Off Ver
    Office 365
    Posts
    2,803

    Re: Count in multiple columns if one condition is met in a different column

    It's always best to attach a file as explained in the yellow banner at the top, but failing that, will this formula work?

    =SUMPRODUCT((N2:N65="Daniel")*(O2:S65<>""))

  6. #6
    Registered User
    Join Date
    10-18-2012
    Location
    Chattanooga
    MS-Off Ver
    Excel 2007
    Posts
    6

    Re: Count in multiple columns if one condition is met in a different column

    I should have been more descriptive... This works but i'm needed it to count multiple columns for each person (q2:q65, r2:r65 and t2:t65). I've attached an example. Need header "other" at the bottom to count for each person how many times data appears in columns q,r & t.
    Attached Files Attached Files

  7. #7
    Forum Guru
    Join Date
    09-10-2017
    Location
    Chippenham, England
    MS-Off Ver
    365
    Posts
    15,029

    Re: Count in multiple columns if one condition is met in a different column

    Maybe
    =SUMPRODUCT(($N$2:$N$65=$B67)*(($Q$2:$Q$65<>"")+($R$2:$R$65<>"")+($T$2:$T$65<>"")))

+ 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. [SOLVED] Merge multiple columns into a single column with condition
    By arindamsenaxa in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 11-17-2017, 10:07 AM
  2. [SOLVED] COUNT across multiple columns with single column criteria
    By AndyGW in forum Excel General
    Replies: 3
    Last Post: 08-13-2017, 08:36 AM
  3. Replies: 6
    Last Post: 08-19-2015, 07:46 PM
  4. Populate 2-column data to multiple columns based on one condition
    By hcyeap in forum Excel Formulas & Functions
    Replies: 10
    Last Post: 12-26-2013, 10:42 AM
  5. Replies: 8
    Last Post: 01-30-2013, 02:58 PM
  6. Calculate Count based on multiple Column condition
    By coolguy01 in forum Excel General
    Replies: 0
    Last Post: 09-10-2012, 06:32 PM
  7. Count no of data present in a specified column based on multiple condition
    By SubhasreeM in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 03-27-2012, 06:24 AM

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