+ Reply to Thread
Results 1 to 10 of 10

Counting Columns Based on Criteria

  1. #1
    Registered User
    Join Date
    07-23-2020
    Location
    Virginia
    MS-Off Ver
    16.0.11328.20478
    Posts
    5

    Counting Columns Based on Criteria

    New user here, and I'm stumped. I have two sheets in the same workbook. For simplicity, on Sheet2 I have cells (A2:D12) displaying text from a formula. The header over each column (A, B, C and D) are dates. On Sheet1, I have multiple dates on a row. Below the dates, I need a formula that will match the overhead date with the date on Sheet2 and then count the cells displaying text in the corresponding column.

    The formula =COUNTIF('Sheet2'!A:A,"*"&A1&"*") produces the number I need. I just need it match dates and find the column and then perform that formula in that column. Thank you so much!
    Last edited by VirginiaABC; 07-26-2020 at 07:49 AM.

  2. #2
    Forum Guru
    Join Date
    03-02-2006
    Location
    Los Angeles, Ca
    MS-Off Ver
    WinXP/MSO2007;Win10/MSO2016
    Posts
    12,572

    Re: Counting Columns Based on Criteria

    (How to) Upload a Workbook directly to the Forum
    (please do not post pictures or links to worksheets)
    • Click Advanced next to "Post Quick Reply" button at the bottom right of the editor box.
    • Scroll down until you see "Manage Attachments",
    • Click the "Choose" button at the upper left (upload from your computer).
    • Select your file, click "open", click "upload"
    • Once the upload is completed the file name will appear below the input boxes in this window.
    • Close the Attachment Manager window.
    • Click "Submit Reply"
    Note: Please do not attach password protected workbooks/worksheets
    Ensure to disable any Workbook Open/Autorun macros before attaching!
    Ben Van Johnson

  3. #3
    Registered User
    Join Date
    07-23-2020
    Location
    Virginia
    MS-Off Ver
    16.0.11328.20478
    Posts
    5

    Re: Counting Columns Based on Criteria

    See attached workbook example. Thank you.
    Attached Files Attached Files

  4. #4
    Forum Expert
    Join Date
    09-25-2015
    Location
    Milan Italy
    MS-Off Ver
    office 365
    Posts
    1,776

    Re: Counting Columns Based on Criteria

    A
    B
    C
    D
    E
    F
    G
    H
    I
    J
    K
    L
    M
    1
    Yes
    8-Jun
    22-Jun
    6-Jul
    20-Jul
    3-Aug
    17-Aug
    31-Aug
    14-Sep
    28-Sep
    sheet1
    2
    0
    0
    0
    3
    8
    3
    8
    0
    0


    B2=IF(B$1<>"",SUMPRODUCT((Sheet2!$A$1:$D$1=Sheet1!B$1)*(Sheet2!$A$2:$D$12=Sheet1!$A$1)),"")


    Copy across

  5. #5
    Registered User
    Join Date
    07-23-2020
    Location
    Virginia
    MS-Off Ver
    16.0.11328.20478
    Posts
    5

    Re: Counting Columns Based on Criteria

    OK, this is great! However, I should have adjusted the excel sheet a little different. Instead of looking for "Yes" I need it find where "Yes" is contained. For instance if Yessir was in a block, it would count that. So instead of saying "=" $A$1, would I use some sort of wildcard? I tried "*" but didn't work...

  6. #6
    Forum Expert
    Join Date
    09-25-2015
    Location
    Milan Italy
    MS-Off Ver
    office 365
    Posts
    1,776

    Re: Counting Columns Based on Criteria

    Can you post a real file ?

  7. #7
    Registered User
    Join Date
    07-23-2020
    Location
    Virginia
    MS-Off Ver
    16.0.11328.20478
    Posts
    5

    Re: Counting Columns Based on Criteria

    See updated attachment. Thank you!
    Attached Files Attached Files

  8. #8
    Forum Expert
    Join Date
    09-25-2015
    Location
    Milan Italy
    MS-Off Ver
    office 365
    Posts
    1,776

    Re: Counting Columns Based on Criteria

    Sheet1


    B2=IF(B$1<>"",SUMPRODUCT((ISNUMBER(SEARCH($A$1,Sheet2!$A$2:$D$12))*(Sheet2!$A$1:$D$1=Sheet1!B$1))),"")


    copy across

  9. #9
    Forum Guru
    Join Date
    03-02-2006
    Location
    Los Angeles, Ca
    MS-Off Ver
    WinXP/MSO2007;Win10/MSO2016
    Posts
    12,572

    Re: Counting Columns Based on Criteria

    Please Login or Register  to view this content.

  10. #10
    Registered User
    Join Date
    07-23-2020
    Location
    Virginia
    MS-Off Ver
    16.0.11328.20478
    Posts
    5

    Re: Counting Columns Based on Criteria

    Awesome!!! Works beautifully!!!

+ 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] Counting cells based on criteria in 2 columns
    By cathb23 in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 01-25-2013, 05:07 PM
  2. [SOLVED] Counting using criteria from two columns
    By wheelman55 in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 11-08-2012, 09:54 PM
  3. [SOLVED] Counting based on multiple criteria and columns
    By Big_R in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 09-17-2012, 10:43 AM
  4. [SOLVED] Counting data acros columns based on criteria
    By ola7mat in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 06-13-2012, 10:37 AM
  5. Replies: 6
    Last Post: 10-18-2011, 07:34 PM
  6. Counting different criteria in different columns ??
    By snowee01 in forum Excel - New Users/Basics
    Replies: 2
    Last Post: 07-10-2009, 09:13 AM
  7. Counting Using Criteria From Two or More Columns
    By Karleajensar in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 06-12-2007, 01:57 PM
  8. [SOLVED] Help w/ counting multiple columns based on IF criteria
    By ConstructionGuy in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 11-08-2005, 12:10 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