+ Reply to Thread
Results 1 to 3 of 3

COUNTIFS for Text Occurences (with Non-defined TEXT) based on Multiple Criteria

  1. #1
    Registered User
    Join Date
    07-09-2011
    Location
    Manheim
    MS-Off Ver
    Excel 2010
    Posts
    23

    COUNTIFS for Text Occurences (with Non-defined TEXT) based on Multiple Criteria

    Hi,

    I have searched the whole forum for a solution to the following issue, I am struggling to deal with, without any success.

    I have a column A2:A300000 --> Months (January)
    I have a column B2:B300000 --> Names (Characters)
    I have a column AB2:AB300000 --> Items (Numbers)
    I have a column I2:I9000 --> Locations (names include both Numbers, Text and the character #)

    I want to calculate an average by counting the locations based on the defined month and name. Because One name maybe have more than one locations.

    =SUMPRODUCT(--('RAW Data'!$A$2:$A$300000=A16),--('RAW Data'!$B$2:$B$300000=$B$17),--('RAW Data'!$AB$2:$AB$300000))/COUNTIFS('RAW Data'!$A$2:$A$300000,A16,'RAW Data'!$B$2:$B$300000,$B$17,'RAW Data'!$I2:$I300000,++++it needs criteria here but I do not know what to include since there are no available criteria eg. B18 or B19+++++)

    Any help please? I have looked on internet and I couldnt find anything because most users use defined criteria according to a specific text they are looking to count and the function COUNTA doesnt work with multiple criteria. Lastly, the SUMPRODUCT in denominator does not work so I cant find a way to fix my spreadsheet. Thank you very much in advance

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

    Re: COUNTIFS for Text Occurences (with Non-defined TEXT) based on Multiple Criteria

    Not sure I get the whole picture, is column I a mix of numbers and letters in each cell or do you mean there are numbers in some cells, letters in others? If it's the former you can probably use AVERAGEIFS function, whereby "*" can be used to signify any text value

    =AVERAGEIFS('RAW Data'!$AB$2:$AB$300000,'RAW Data'!$A$2:$A$300000,A16,'RAW Data'!$B$2:$B$300000,$B$17,'RAW Data'!$I$2:$I$300000,"*")

    or if it's the latter try this "array formula"

    =AVERAGE(IF('RAW Data'!$A$2:$A$300000=A16,IF('RAW Data'!$B$2:$B$300000=$B$17,IF('RAW Data'!$I$2:$I$300000<>"",'RAW Data'!$AB$2:$AB$300000))))

    confirmed with CTRL+SHIFT+ENTER
    Audere est facere

  3. #3
    Registered User
    Join Date
    07-09-2011
    Location
    Manheim
    MS-Off Ver
    Excel 2010
    Posts
    23

    Re: COUNTIFS for Text Occurences (with Non-defined TEXT) based on Multiple Criteria

    Thanks but it isnt that I want to get.

    The cells of Column I contain both text and numerical (not numerical or text but both). I should have included a sample for a better understanding.

    For the nominator, I have the SUMPRODUCT for the Items which works great.

    Actually, for the denominator I want to get the unique values of Column I (location) based on the Month and the Name.

    What I have done for Column B is to get the month in jan-10, feb-10, etc..all these are computed from a reference eg. 02/02/2010 --> Feb-10
    I have done this transformation for my month.

    Related to the locations, I want the count of the locations based on the dates (of the month) and a specific name so I was wondering if there any dream-like way to fix it.

    Thank you very much.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

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