+ Reply to Thread
Results 1 to 4 of 4

Formula for counting # of times 2 sets of data shows up

  1. 10-28-2016, 10:49 AM #1
    Statz
    Statz is offline
    Forum Contributor
    Join Date
    02-26-2010
    Location
    United States
    MS-Off Ver
    Excel 2013
    Posts
    164

    Formula for counting # of times 2 sets of data shows up

    In the attachment you'll see some information by day & month. It lists what meals were for that day.

    What I'd like to do is have a formula that calculates how many times a certain meal shows up in a specific month.

    So for January how many times does "PBJ" show up. Then February etc.

    I'd like to calculate this on a different tab so I can have a snap shot of all that data. I'm hoping there is a way to just click on the cell instead of typing "PBJ" & "January". This way I could easily copy down or over and keep the formula I'm looking for.

    Thanks
    Attached Files Attached Files
    Last edited by Statz; 10-31-2016 at 11:16 AM.
    Reply With QuoteRegister To Reply
  2. 10-28-2016, 11:03 AM #2
    Glenn Kennedy
    Glenn Kennedy is offline
    Forum Moderator Glenn Kennedy's Avatar
    Join Date
    07-08-2012
    Location
    Digital Nomad... occasionally based in Ireland.
    MS-Off Ver
    O365 (PC) V 2406
    Posts
    44,213

    Re: Formula for counting # of times 2 sets of data shows up

    In B2, copied across and down:

    =SUMPRODUCT(('2016DietLog'!$A$3:$A$15=Sheet1!$A2)*('2016DietLog'!$D$3:$K$15=Sheet1!B$1))
    Glenn



    None of us get paid for helping you... we do this for fun. So DON'T FORGET to say "Thank You" to all who have freely given some of their time to help YOU
    Reply With QuoteRegister To Reply
  3. 10-28-2016, 11:12 AM #3
    Pete_UK
    Pete_UK is offline
    Forum Guru Pete_UK's Avatar
    Join Date
    12-31-2011
    Location
    Warrington, England
    MS-Off Ver
    Office 2019 (still learning)
    Posts
    24,822

    Re: Formula for counting # of times 2 sets of data shows up

    Put the date 01/01/2016 in cell A2 of Sheet1 and this formula in A3:

    =DATE(YEAR(A2),MONTH(A2)+1,1)

    Apply a Custom Format of "mmmm" to those cells, then copy A3 down as far as you need to.

    Then in B2 you can use this formula:

    =SUMPRODUCT((('2016DietLog'!$D$3:$J$100=B$1)*('2016DietLog'!$B$3:$B$100>=$A2)*('2016DietLog'!$B$3:$B$100<=EOMONTH($A2,0))))

    and copy this across and down as required.

    Hope this helps.

    Pete
    Reply With QuoteRegister To Reply
  4. 10-31-2016, 11:17 AM #4
    Statz
    Statz is offline
    Forum Contributor
    Join Date
    02-26-2010
    Location
    United States
    MS-Off Ver
    Excel 2013
    Posts
    164

    Re: Formula for counting # of times 2 sets of data shows up

    Thank you both for the replies. I used the first suggested formula and that's working. Pete. I appreciate your response but the formula you had seems to be busier work that looks like I won't need. Is there something about your formula that stands out from the previous post?

    Thanks
    Reply With QuoteRegister To Reply
+ Reply to Thread
« Previous Thread | Next Thread »

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. Modifiting Counting Times Formula
    By adinek22 in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 11-20-2015, 12:41 AM
  2. [SOLVED] COUNTIF - counting 2 sets of data?
    By heatwave in forum Excel General
    Replies: 5
    Last Post: 03-20-2015, 09:26 AM
  3. Counting the number of characters that match between 2 data sets
    By cipherx2000 in forum Excel General
    Replies: 5
    Last Post: 06-23-2011, 12:32 PM
  4. Formula to show how many times a name shows up
    By Ajh in forum Excel General
    Replies: 1
    Last Post: 11-09-2010, 04:06 PM
  5. Counting data sets based on multiple criteria
    By hjb0802 in forum Excel Formulas & Functions
    Replies: 5
    Last Post: 04-29-2010, 10:47 AM
  6. percent of times data shows in a column
    By adamsj in forum Excel Formulas & Functions
    Replies: 6
    Last Post: 03-12-2007, 05:08 PM
  7. [SOLVED] Counting # of Times an Item Shows Up in a Table
    By Ralph in forum Excel Formulas & Functions
    Replies: 19
    Last Post: 12-18-2005, 10:30 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

Forum Rules


Search Engine Friendly URLs by vBSEO 3.6.0 RC 1