+ Reply to Thread
Results 1 to 4 of 4

sum count if formula for three criteria that delivers a different number for each one

  1. #1
    Registered User
    Join Date
    07-29-2014
    Location
    London, England
    MS-Off Ver
    2010
    Posts
    2

    sum count if formula for three criteria that delivers a different number for each one

    I have tried a million ways of looking for this and have done a work around by putting it in a hidden column but I'm sure there is another way! I have columns with either y, yy or yyy in each cell. I want the bottom row to add up the number of ys so if "y" then 1, if "yy", then 2, if "yyy" then 3 and add them up. like this:

    y
    yy
    yyy
    6

    I have this so far

    =SUM(COUNTIF(C2:C43,"y"),COUNTIF(C2:C43,"yy"),COUNTIF(C2:C43,"yyy"))

    All that gives me is the sum of every occurence rather than a total of ys like this (I don't want this version):

    y
    yy
    yyy
    3

    I'm sorry if none of that made the blindest bit of sense. Any help would be very gratefully received.

    Thanks

  2. #2
    Valued Forum Contributor Hawkeye16's Avatar
    Join Date
    02-27-2013
    Location
    Holland
    MS-Off Ver
    ├┤ Pew Pew
    Posts
    441

    Re: sum count if formula for three criteria that delivers a different number for each one

    Assuming the y, yy, yyy are in A1:A3 you could use

    Formula: copy to clipboard
    Please Login or Register  to view this content.


    This is an array formula and you need to press Ctrl + Shift + Enter to compute
    Despite the high cost of living, it remains very popular.

    Don't forget to mark threads SOLVED when you get an answer and rep all the geniouses that helped you today!

  3. #3
    Forum Moderator daddylonglegs's Avatar
    Join Date
    01-14-2006
    Location
    England
    MS-Off Ver
    2016
    Posts
    14,676

    Re: sum count if formula for three criteria that delivers a different number for each one

    This version will count all lower case "y"s in the range

    =SUMPRODUCT(LEN(C2:C43)-LEN(SUBSTITUTE(C2:C43,"y","")))

    If you want to count both "Y" and "y" use this one

    =SUMPRODUCT(LEN(C2:C43)-LEN(SUBSTITUTE(LOWER(C2:C43),"y","")))

    ....or if the only possibilities are "y", "yy" or "yyy" then you can use this version

    =SUM(COUNTIF(C2:C43,{"y","yy","yyy"})*{1,2,3})
    Last edited by daddylonglegs; 07-29-2014 at 09:02 AM.
    Audere est facere

  4. #4
    Registered User
    Join Date
    07-29-2014
    Location
    London, England
    MS-Off Ver
    2010
    Posts
    2

    Re: sum count if formula for three criteria that delivers a different number for each one

    Ow wow that's amazing. Thank you sooo much. Am ever so grateful.

+ 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] Formula to use to count the number of cells in a column which meet three sets of criteria
    By Dhabitude in forum Excel Formulas & Functions
    Replies: 12
    Last Post: 12-11-2013, 11:22 AM
  2. [SOLVED] Count word/number without criteria
    By thisisgerald in forum Excel Formulas & Functions
    Replies: 12
    Last Post: 09-06-2012, 06:22 AM
  3. FormulaArray Which Delivers Text Instead of Formula
    By inwalkedbud in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 10-04-2007, 02:05 PM
  4. I Need to Count Number of Entries Based on Two Criteria
    By Jones in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 07-14-2005, 05:34 PM
  5. [SOLVED] how can i count a number that meets a criteria?
    By oakm in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 03-11-2005, 03:07 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