+ Reply to Thread
Results 1 to 4 of 4

Countif Function to work similarly to Sumif Function

  1. #1
    Registered User
    Join Date
    01-24-2019
    Location
    Alabama
    MS-Off Ver
    2017
    Posts
    2

    Question Countif Function to work similarly to Sumif Function

    excel snippit.PNG

    Hello,

    In cell O14 I would like to write a formula that looks at Row 1, chooses the cells that matches criteria in cell A14, then counts the "P", "X", and "T" in the corresponding columns. In my head this is similar to a sumif where it matches the criteria in array, to sum the contents of another array.

    Thanks for your help.
    Attached Images Attached Images

  2. #2
    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: Countif Function to work similarly to Sumif Function

    It would help if you attached a sample Excel workbook, rather than a picture of one.

    To do this, click on Go Advanced (below the Edit Window) while you are composing a reply, then scroll down to and click on Manage Attachments and the Upload window will open. Click on Browse and navigate to (and double-click) the file icon that you want to attach, then click on Upload and then on Close this Window to return to the Edit window. When you have finished composing your post, click on Submit Post. Don't try to use the Paperclip icon, as it doesn't work on this forum.

    I think you might need to use SUMPRODUCT instead.

    Hope this helps.

    Pete

  3. #3
    Registered User
    Join Date
    01-24-2019
    Location
    Alabama
    MS-Off Ver
    2017
    Posts
    2

    Re: Countif Function to work similarly to Sumif Function

    Attached is the workbook. Thanks for the detailed instructions.
    Attached Files Attached Files

  4. #4
    Forum Expert 63falcondude's Avatar
    Join Date
    08-22-2016
    Location
    USA
    MS-Off Ver
    365
    Posts
    6,266

    Re: Countif Function to work similarly to Sumif Function

    O14 =SUMPRODUCT((B1:AC1=A14)*(B3:AC9<>"U"))/SUMPRODUCT((B1:AC1=A14)*(B3:AC9<>""))

    Also, F14 can be shortened to this:
    =SUM(COUNTIF(B3:ZZ9,{"P","T","X"}))/COUNTA(B3:ZZ9)
    or even this:
    =COUNTIF(B3:ZZ9,"<>U")/COUNTA(B3:ZZ9)

+ 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] Sumif And countif combined function
    By Deventus in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 11-27-2013, 05:11 AM
  2. How to use Subtotal function within in a COUNTIF & SUMIF formula
    By lindafinlay in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 10-24-2013, 02:27 AM
  3. How do I add a countif, averageif and sumif function
    By susiesc in forum Excel Formulas & Functions
    Replies: 6
    Last Post: 03-29-2013, 12:11 AM
  4. The SUMIF & COUNTIF Function
    By ugaskidawg in forum Excel Formulas & Functions
    Replies: 8
    Last Post: 06-07-2008, 09:30 AM
  5. countif function or sumif
    By tipuser in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 07-08-2007, 12:51 PM
  6. [SOLVED] countif / sumif function error
    By Mark J. in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 05-04-2006, 05:55 PM
  7. [SOLVED] Is there a function Countif that operates like Sumif?
    By Undrline in forum Excel Formulas & Functions
    Replies: 7
    Last Post: 04-12-2006, 01:00 AM
  8. Can I use a Reference inside a SUMIF or COUNTIF Function?
    By Albie in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 11-13-2005, 06:25 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