+ Reply to Thread
Results 1 to 9 of 9

Problem counting cells with text in a range if criteria in column have been met

  1. #1
    Registered User
    Join Date
    04-20-2017
    Location
    Christchurch, Dorset
    MS-Off Ver
    2016
    Posts
    29

    Problem counting cells with text in a range if criteria in column have been met

    I have attached a file with a sample of the spreadsheet I'm working on. I have shown the 3 formulas that I am trying to write in the spreadsheet to hopefully make it clear what I'm after. I've tried countifs and tried sumproduct but neither appear to be able to cope with counting a range rather than a column.

    The 3 formulas required are all counting the cells E2:H15 if they have text

    1) if there is a Y in column C
    2)if there is a K or an E in column B
    3) If there is a Y in columns A or D

    I can make it work using countifs and sumproduct if I am just counting one column rather than a complete range but wondering if it is possible to do what I want?

    Thanks

    Sue xx
    Attached Files Attached Files

  2. #2
    Forum Expert
    Join Date
    03-23-2004
    Location
    London, England
    MS-Off Ver
    Excel 2019
    Posts
    7,062

    Re: Problem counting cells with text in a range if criteria in column have been met

    Does this work?

    in I20
    =SUMPRODUCT((E2:H15<>"")*(C2:C15="Y"))

    in I24
    =SUMPRODUCT((E2:H15<>"")*((B2:B15="K")+(B2:B15="E")))

    in I28
    =SUMPRODUCT((E2:H15<>"")*((A2:A15="Y")+(D2:D15="Y")))
    Regards
    Special-K

    Ensure you describe your problem clearly, I have little time available to solve these problems and do not appreciate numerous changes to them.

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

    Re: Problem counting cells with text in a range if criteria in column have been met

    Try these:

    EAL Count =SUMPRODUCT((C2:C15="Y")*(E2:H15<>""))

    SEN Count =SUMPRODUCT(((B2:B15="K")+(B2:B15="E"))*(E2:H15<>""))

    PP & FSM Count =SUMPRODUCT(((A2:A15="Y")+(D2:D15="Y"))*(E2:H15<>""))

    Edit: PP & FSM Count double counts... Working on a fix.
    Last edited by 63falcondude; 02-22-2018 at 12:32 PM.

  4. #4
    Forum Expert
    Join Date
    05-05-2015
    Location
    UK
    MS-Off Ver
    Microsoft? Excel? for Microsoft 365 MSO (Version 2402 Build 16.0.17328.20068) 64-bit
    Posts
    28,007

    Re: Problem counting cells with text in a range if criteria in column have been met

    For PP &FSM

    try

    =SUMPRODUCT(--($E$2:$H$15<>"")*(($A$2:$A$15="Y")+($D$2:$D$15="Y"))-(($A$2:$A$15="Y")*($D$2:$D$15="Y")))

  5. #5
    Forum Expert ben_hensel's Avatar
    Join Date
    01-26-2012
    Location
    Northeast USA
    MS-Off Ver
    Office 365
    Posts
    2,043

    Re: Problem counting cells with text in a range if criteria in column have been met

    With array formulas:

    formula 1:
    Please Login or Register  to view this content.
    formula 2:
    Please Login or Register  to view this content.
    formula 3:
    Please Login or Register  to view this content.
    I got a little sloppy and just took advantage of how IF assesses any number<>0 as TRUE for the OR condition in the first term. Then it was Boolean forcing on the TRUE term of the IF, feeding the numbers into the SUM.

    As these are array formulas, so they must be confirmed into the cell with CTRL+SHIFT+ENTER, not just the ENTER key.
    Click the [* Add Reputation] Button to thank people for help!
    Post Attachment: Click [Go Advanced] under the reply box; then click [Manage Attachments] to launch the attachments dialogue.
    Mark Thread Solved: Go to the top of the first post; Select [Thread Tools] dropdown; Pick the [Mark thread as Solved] option.

  6. #6
    Registered User
    Join Date
    04-20-2017
    Location
    Christchurch, Dorset
    MS-Off Ver
    2016
    Posts
    29

    Re: Problem counting cells with text in a range if criteria in column have been met

    Thank you everyone - I thought there was a solution to the third formula but I have found that it's not quite there yet. By changing the data to make sure I have covered every eventuality I have realised that it is not calculating correctly if there are blank cells in the range (E2:H15) and there is a Y in both columns A and D.

    Sorry

    Sue
    Last edited by SueBristow; 02-22-2018 at 06:07 PM. Reason: Not quite solved after all - sorry

  7. #7
    Forum Expert
    Join Date
    05-05-2015
    Location
    UK
    MS-Off Ver
    Microsoft? Excel? for Microsoft 365 MSO (Version 2402 Build 16.0.17328.20068) 64-bit
    Posts
    28,007

    Re: Problem counting cells with text in a range if criteria in column have been met

    Try

    =SUMPRODUCT(--($E$2:$H$15<>"")*(($A$2:$A$15="Y")+($D$2:$D$15="Y"))-(($A$2:$A$15="Y")*($D$2:$D$15="Y")*(($E$2:$H$15<>""))))

  8. #8
    Registered User
    Join Date
    04-20-2017
    Location
    Christchurch, Dorset
    MS-Off Ver
    2016
    Posts
    29

    Re: Problem counting cells with text in a range if criteria in column have been met

    Thank you - that seems to work so far

    Now why couldn't I work that out

  9. #9
    Forum Guru xlnitwit's Avatar
    Join Date
    06-27-2016
    Location
    London
    MS-Off Ver
    Windows: 2010; Mac: 16.13 (O365)
    Posts
    7,085

    Re: Problem counting cells with text in a range if criteria in column have been met

    Or perhaps
    =SUMPRODUCT(--($E$2:$H$15<>"")*((($A$2:$A$15="Y")+($D$2:$D$15="Y"))>0))
    Don
    Please remember to mark your thread 'Solved' when appropriate.

+ 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. Replies: 4
    Last Post: 06-13-2014, 07:08 PM
  2. Counting Text based on specific criteria in another column
    By djreddy in forum Excel Formulas & Functions
    Replies: 26
    Last Post: 08-26-2013, 09:35 AM
  3. Counting Unique text in column B based on a criteria from column a
    By clocmasta in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 06-18-2013, 06:40 PM
  4. Counting cells that contain a numerical range when Criteria is met
    By DLGS in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 03-12-2013, 03:47 PM
  5. [SOLVED] Problem with counting multiple cells with muliple criteria
    By dbogey in forum Excel Formulas & Functions
    Replies: 21
    Last Post: 12-03-2012, 01:27 PM
  6. [SOLVED] counting text in a column w/text criteria to other column
    By LnghairdFreak in forum Excel Formulas & Functions
    Replies: 6
    Last Post: 11-20-2006, 04:37 PM
  7. [SOLVED] Counting Occurrence of Text within Text in Cells in Range.
    By Jeremy N. in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 09-08-2005, 01:05 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