+ Reply to Thread
Results 1 to 10 of 10

Counting instances in one cell range for days of week in another range

  1. #1
    Registered User
    Join Date
    04-07-2014
    Location
    London UK
    MS-Off Ver
    Excel 2003
    Posts
    5

    Counting instances in one cell range for days of week in another range

    Hi, all.

    I have a spreadsheet that lists every Friday, Saturday, and Sunday for each month of 2015 in column B. The cells are date-formatted and display like this

    Fri 02 Jan 15

    For each entry in column B, column C can contain one of four character entries, 'F', 'P', 'MM', or 'PF', or it can be empty.

    At the foot of each month I need to to show the total number of F's (see note below), P's and PF's for Fridays, Saturdays, and Sundays, like this

    F P PF
    Fri 2 2 0
    Sat 1 3 1
    Sun 2 0 0

    Note: The 'F' column needs to show the totals for 'F' and 'MM' values combined.

    Can anyone recommend a formula that will do this for me at all?

    Thank you,

    Goth

  2. #2
    Forum Expert XOR LX's Avatar
    Join Date
    04-18-2013
    Location
    Turin, Italy
    MS-Off Ver
    Office 365
    Posts
    7,742

    Re: Counting instances in one cell range for days of week in another range

    Hi,

    The formulas itself are not the issue.

    It's knowing where they should be placed and over which range of cells they should be calculating.

    If you could provide an actual workbook with some examples and your desired results clearly outlined I'm sure it won't take long to get you a solution.

    Regards
    Click * below if this answer helped

    Advanced Excel Techniques: http://excelxor.com/

  3. #3
    Registered User
    Join Date
    04-07-2014
    Location
    London UK
    MS-Off Ver
    Excel 2003
    Posts
    5

    Re: Counting instances in one cell range for days of week in another range

    Sample file attached as requested.

    Thx
    Attached Files Attached Files

  4. #4
    Forum Expert XOR LX's Avatar
    Join Date
    04-18-2013
    Location
    Turin, Italy
    MS-Off Ver
    Office 365
    Posts
    7,742

    Re: Counting instances in one cell range for days of week in another range

    Thanks.

    Based on that attachment:

    In C22 and copy down:

    =SUMPRODUCT(0+(TEXT(JanDoW,"dddd")=SUBSTITUTE($B22,"s","")))

    In D22 and copy down and across:

    =SUMPRODUCT(0+(TEXT(JanDoW,"dddd")=SUBSTITUTE($B22,"s","")),0+ISNUMBER(SEARCH(JanCode,D$20)))

    Regards

  5. #5
    Forum Expert bebo021999's Avatar
    Join Date
    07-22-2011
    Location
    Vietnam
    MS-Off Ver
    Excel 2016
    Posts
    9,409

    Re: Counting instances in one cell range for days of week in another range

    D22:
    =SUMPRODUCT((TEXT($B$4:$B$17,"dddd")&"s"=$B22)*((JanCode="F")+(JanCode="MM")))
    E22:
    =SUMPRODUCT((TEXT($B$4:$B$17,"dddd")&"s"=$B22)*(JanCode="P"))
    F22:
    =SUMPRODUCT((TEXT($B$4:$B$17,"dddd")&"s"=$B22)*(JanCode="PF"))

    Drag down
    Quang PT

  6. #6
    Registered User
    Join Date
    04-07-2014
    Location
    London UK
    MS-Off Ver
    Excel 2003
    Posts
    5

    Re: Counting instances in one cell range for days of week in another range

    XOR LX,

    Thank you for the very quick response.

    I tried what you suggested this but it's not working for me, I'm afraid. I think I've copied your formulae over correctly but I'm not getting correct totals for PF, blank entries in the range JanCode cause confusion,and if I populate all the JanCode cells with P, F, or MM, I still get totals for PF when the answers for this should all be zero. If all the JanCode cells are empty, the formula still returns 5, 5 and 4 for each code for Fridays, Saturdays, and Sundays.

    Any ideas?

  7. #7
    Forum Expert XOR LX's Avatar
    Join Date
    04-18-2013
    Location
    Turin, Italy
    MS-Off Ver
    Office 365
    Posts
    7,742

    Re: Counting instances in one cell range for days of week in another range

    Sure. If you could re-attach the precise same workbook that you posted earlier but with your attempts at pasting my formulas in I'm sure I'll be able to sort it.

    Cheers.

  8. #8
    Registered User
    Join Date
    04-07-2014
    Location
    London UK
    MS-Off Ver
    Excel 2003
    Posts
    5

    Re: Counting instances in one cell range for days of week in another range

    bebo021999,

    Thanks for your reply, your formulae appear to be doing what I was after as far as counting up the total numbers of the various codes for Fridays, Saturdays, and Sundays is concerned.

    What are your thoughts for counting up the total numbers of Fridays, Saturdays, and Sundays in in the range JanDoW? The method given above by XOR LX returns the correct figures in my sample file, I was just wondering if there might be any alternative solutions to be considered.

    Cheers.

  9. #9
    Registered User
    Join Date
    04-07-2014
    Location
    London UK
    MS-Off Ver
    Excel 2003
    Posts
    5

    Re: Counting instances in one cell range for days of week in another range

    XOR LX...

    Sorry for the delay in getting back to you. Add'l copy of file attached as requested.

    Regards,

    Goth
    Attached Files Attached Files

  10. #10
    Forum Expert XOR LX's Avatar
    Join Date
    04-18-2013
    Location
    Turin, Italy
    MS-Off Ver
    Office 365
    Posts
    7,742

    Re: Counting instances in one cell range for days of week in another range

    Hi,

    Apologies. Try this slight amendment to the formula for D22:

    =SUMPRODUCT(0+(TEXT(JanDoW,"dddd")=SUBSTITUTE($B22,"s","")),0+ISNUMBER(SEARCH(" "&JanCode&" "," "&D$20&" ")))

    Again, copy to the right and down. The formula for column C should not require any changes.

    Regards

+ 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. counting the number of instances of blank cells in a range
    By moses67 in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 05-01-2013, 10:35 AM
  2. [SOLVED] Counting how many dates in a range fall into this week and last week
    By AneelK in forum Excel Formulas & Functions
    Replies: 5
    Last Post: 01-08-2013, 09:38 AM
  3. Separating days by Year/Week in a date range :)
    By scotinexcile in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 08-21-2012, 09:26 PM
  4. Counting and adding instances in a cell range
    By RockyRoad in forum Excel General
    Replies: 9
    Last Post: 01-20-2012, 11:52 AM
  5. Counting the instances of months in a range of dates
    By montek in forum Excel General
    Replies: 4
    Last Post: 12-25-2011, 12:10 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