+ Reply to Thread
Results 1 to 20 of 20

Need help with formula for counting incidents with multiple criteria

  1. #1
    Forum Contributor
    Join Date
    01-04-2013
    Location
    Arkansas
    MS-Off Ver
    Microsoft Excel 2016
    Posts
    252

    Need help with formula for counting incidents with multiple criteria

    First let me say thanks in advance. I have the formulas close, but got lost trying to extract the data by month.

    On the Cover Tab, I need E7 to look for the name in A7 and Match it with the name in column D on the 2014 Tab. Look for the date in E5 Feb2014), add all February dates in column E on Tab 2014 for that name and Count the incidents in column F on Tab 2014.

    On the Cover Tab, I need E45 to look for what is typed into K41 (that information is in column D of the year Tabs), look at E43 (year) and D45 (month), add the incidents up in Column C for that month from that Tab (INT# on Cover Tab is ASSOCIATE THEFT on the year tabs).

    I have attached what I am working on.
    Attached Files Attached Files

  2. #2
    Valued Forum Contributor
    Join Date
    04-22-2014
    Location
    Auckland, New Zealand
    MS-Off Ver
    Office 365 (work) and Excel 2013 (home)
    Posts
    1,167

    Re: Need help with formula for counting incidents with multiple criteria

    In E7: =COUNTIFS('2014'!$D:$D,Cover!$A7,'2014'!$E:$E,">="&DATEVALUE(E5),'2014'!$E:$E,"<"&DATE(YEAR(DATEVALUE(E5)+31),MONTH(DATEVALUE(E5)+31),1))
    in F7: =SUMIFS('2014'!G:G,'2014'!$D:$D,Cover!$A7,'2014'!$E:$E,">="&DATEVALUE(E5),'2014'!$E:$E,"<"&DATE(YEAR(DATEVALUE(E5)+31),MONTH(DATEVALUE(E5)+31),1))

    Still working on E45.

  3. #3
    Valued Forum Contributor
    Join Date
    04-22-2014
    Location
    Auckland, New Zealand
    MS-Off Ver
    Office 365 (work) and Excel 2013 (home)
    Posts
    1,167

    Re: Need help with formula for counting incidents with multiple criteria

    Is the 2010 year from Feb 2010 - Jan 2011, or Feb 2009 - Jan 2010?

  4. #4
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 7/10/13/16/365 (PC ver 2310)
    Posts
    53,044

    Re: Need help with formula for counting incidents with multiple criteria

    If you use actual dates in your header row (instead of text), it makes calcs much simpler. You can then you can use these for the apps and Dollars, copied down and (in pairs) across...
    E7=SUMIFS('2014'!$F:$F,'2014'!$D:$D,$A7,'2014'!$E:$E,">="&E$5,'2014'!$E:$E,"<"&EDATE(E$5,1))
    F7=SUMIFS('2014'!$G:$G,'2014'!$D:$D,$A7,'2014'!$E:$E,">="&E$5,'2014'!$E:$E,"<"&EDATE(E$5,1))

    You would use the same principal for the 2nd table, too
    1. Use code tags for VBA. [code] Your Code [/code] (or use the # button)
    2. If your question is resolved, mark it SOLVED using the thread tools
    3. Click on the star if you think someone helped you

    Regards
    Ford

  5. #5
    Forum Contributor
    Join Date
    01-04-2013
    Location
    Arkansas
    MS-Off Ver
    Microsoft Excel 2016
    Posts
    252

    Re: Need help with formula for counting incidents with multiple criteria

    2010 is from February 1, 2010 through January 31, 2011

  6. #6
    Valued Forum Contributor
    Join Date
    04-22-2014
    Location
    Auckland, New Zealand
    MS-Off Ver
    Office 365 (work) and Excel 2013 (home)
    Posts
    1,167

    Re: Need help with formula for counting incidents with multiple criteria

    Ok, so they're quite complicated formulas because you don't work to a calendar year, but:
    E45: =COUNTIFS(INDIRECT(E$43&"!D:D"),Cover!$K$41&"*",INDIRECT(E$43&"!E:E"),">="&IF(MONTH($D45)=1,DATE(E$43+1,1,1),DATE(E$43,MONTH($D45),1)),INDIRECT(E$43&"!E:E"),"<="&IF(MONTH($D45)=1,EOMONTH(DATE(E$43+1,1,1),0),EOMONTH(DATE(E$43,MONTH($D45),1),0)),INDIRECT(E$43&"!C:C"),"ASSOCIATE THEFT")
    F45: =SUMIFS(INDIRECT(E$43&"!G:G"),INDIRECT(E$43&"!D:D"),Cover!$K$41&"*",INDIRECT(E$43&"!E:E"),">="&IF(MONTH($D45)=1,DATE(E$43+1,1,1),DATE(E$43,MONTH($D45),1)),INDIRECT(E$43&"!E:E"),"<="&IF(MONTH($D45)=1,EOMONTH(DATE(E$43+1,1,1),0),EOMONTH(DATE(E$43,MONTH($D45),1),0)),INDIRECT(E$43&"!C:C"),"ASSOCIATE THEFT")
    G45 and H45 are the same as E45 and F45 respectively except they have NON-ASSOCIATE THEFT at the end. You will need to copy and paste the formulas from E45 and F45 to G45 and H45, not copy and paste the cells, but once you've done those two you should be able to copy and paste cells, or fill down.
    I have also changed D45:D56 and D62:D73 to dates with the Cell Number Format as "Mmm" to make this work.
    Last edited by gak67; 04-28-2014 at 08:32 PM.

  7. #7
    Forum Contributor
    Join Date
    01-04-2013
    Location
    Arkansas
    MS-Off Ver
    Microsoft Excel 2016
    Posts
    252

    Re: Need help with formula for counting incidents with multiple criteria

    gak, The formula for E7 is not counting the numbers in column F on the year tabs.

    Quote Originally Posted by gak67 View Post
    In E7: =COUNTIFS('2014'!$D:$D,Cover!$A7,'2014'!$E:$E,">="&DATEVALUE(E5),'2014'!$E:$E,"<"&DATE(YEAR(DATEVALUE(E5)+31),MONTH(DATEVALUE(E5)+31),1))
    in F7: =SUMIFS('2014'!G:G,'2014'!$D:$D,Cover!$A7,'2014'!$E:$E,">="&DATEVALUE(E5),'2014'!$E:$E,"<"&DATE(YEAR(DATEVALUE(E5)+31),MONTH(DATEVALUE(E5)+31),1))

    Still working on E45.

  8. #8
    Valued Forum Contributor
    Join Date
    04-22-2014
    Location
    Auckland, New Zealand
    MS-Off Ver
    Office 365 (work) and Excel 2013 (home)
    Posts
    1,167

    Re: Need help with formula for counting incidents with multiple criteria

    It is counting the number of times the value in column A of the cover sheet appears in column D of the 2014 sheet, within the date range, which is in effect the same thing. Are you saying you want to sum the numbers in column F?

  9. #9
    Forum Contributor
    Join Date
    01-04-2013
    Location
    Arkansas
    MS-Off Ver
    Microsoft Excel 2016
    Posts
    252

    Re: Need help with formula for counting incidents with multiple criteria

    FDibbins,
    I change the header dates to actual dates. This works great for the first table, I haven't tried the second one yet.

    Quote Originally Posted by FDibbins View Post
    If you use actual dates in your header row (instead of text), it makes calcs much simpler. You can then you can use these for the apps and Dollars, copied down and (in pairs) across...
    E7=SUMIFS('2014'!$F:$F,'2014'!$D:$D,$A7,'2014'!$E:$E,">="&E$5,'2014'!$E:$E,"<"&EDATE(E$5,1))
    F7=SUMIFS('2014'!$G:$G,'2014'!$D:$D,$A7,'2014'!$E:$E,">="&E$5,'2014'!$E:$E,"<"&EDATE(E$5,1))

    You would use the same principal for the 2nd table, too

  10. #10
    Forum Contributor
    Join Date
    01-04-2013
    Location
    Arkansas
    MS-Off Ver
    Microsoft Excel 2016
    Posts
    252

    Re: Need help with formula for counting incidents with multiple criteria

    Yes, the sum.

    Quote Originally Posted by gak67 View Post
    It is counting the number of times the value in column A of the cover sheet appears in column D of the 2014 sheet, within the date range, which is in effect the same thing. Are you saying you want to sum the numbers in column F?

  11. #11
    Forum Contributor
    Join Date
    01-04-2013
    Location
    Arkansas
    MS-Off Ver
    Microsoft Excel 2016
    Posts
    252

    Re: Need help with formula for counting incidents with multiple criteria

    I can't get the date cells in the second table to format as date leaving the text as 2010, 2011, 2012, etc.

    Quote Originally Posted by FDibbins View Post
    If you use actual dates in your header row (instead of text), it makes calcs much simpler. You can then you can use these for the apps and Dollars, copied down and (in pairs) across...
    E7=SUMIFS('2014'!$F:$F,'2014'!$D:$D,$A7,'2014'!$E:$E,">="&E$5,'2014'!$E:$E,"<"&EDATE(E$5,1))
    F7=SUMIFS('2014'!$G:$G,'2014'!$D:$D,$A7,'2014'!$E:$E,">="&E$5,'2014'!$E:$E,"<"&EDATE(E$5,1))

    You would use the same principal for the 2nd table, too

  12. #12
    Valued Forum Contributor
    Join Date
    04-22-2014
    Location
    Auckland, New Zealand
    MS-Off Ver
    Office 365 (work) and Excel 2013 (home)
    Posts
    1,167

    Re: Need help with formula for counting incidents with multiple criteria

    I would leave the Years just as numbers.

  13. #13
    Valued Forum Contributor
    Join Date
    04-22-2014
    Location
    Auckland, New Zealand
    MS-Off Ver
    Office 365 (work) and Excel 2013 (home)
    Posts
    1,167

    Re: Need help with formula for counting incidents with multiple criteria

    E45: =SUMIFS(INDIRECT(E$43&"!F:F"),INDIRECT(E$43&"!D:D"),Cover!$K$41&"*",INDIRECT(E$43&"!E:E"),">="&IF(MONTH($D45)=1,DATE(E$43+1,1,1),DATE(E$43,MONTH($D45),1)),INDIRECT(E$43&"!E:E"),"<="&IF(MONTH($D45)=1,EOMONTH(DATE(E$43+1,1,1),0),EOMONTH(DATE(E$43,MONTH($D45),1),0)),INDIRECT(E$43&"!C:C"),"ASSOCIATE THEFT")
    G45 with the NON-ASSOCIATE THEFT

  14. #14
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 7/10/13/16/365 (PC ver 2310)
    Posts
    53,044

    Re: Need help with formula for counting incidents with multiple criteria

    I have made a start on the 2nd table, using INDIRECT() to get the sheet name. see if you can build on what I have to give you what you want?
    Attached Files Attached Files

  15. #15
    Forum Contributor
    Join Date
    01-04-2013
    Location
    Arkansas
    MS-Off Ver
    Microsoft Excel 2016
    Posts
    252

    Re: Need help with formula for counting incidents with multiple criteria

    gak,
    It's not pulling any numbers up.


    Quote Originally Posted by gak67 View Post
    E45: =SUMIFS(INDIRECT(E$43&"!F:F"),INDIRECT(E$43&"!D:D"),Cover!$K$41&"*",INDIRECT(E$43&"!E:E"),">="&IF(MONTH($D45)=1,DATE(E$43+1,1,1),DATE(E$43,MONTH($D45),1)),INDIRECT(E$43&"!E:E"),"<="&IF(MONTH($D45)=1,EOMONTH(DATE(E$43+1,1,1),0),EOMONTH(DATE(E$43,MONTH($D45),1),0)),INDIRECT(E$43&"!C:C"),"ASSOCIATE THEFT")
    G45 with the NON-ASSOCIATE THEFT

  16. #16
    Valued Forum Contributor
    Join Date
    04-22-2014
    Location
    Auckland, New Zealand
    MS-Off Ver
    Office 365 (work) and Excel 2013 (home)
    Posts
    1,167

    Re: Need help with formula for counting incidents with multiple criteria

    Works for me. Have you changed D45:D56 and D62:D73 to dates rather than text?

  17. #17
    Valued Forum Contributor
    Join Date
    04-22-2014
    Location
    Auckland, New Zealand
    MS-Off Ver
    Office 365 (work) and Excel 2013 (home)
    Posts
    1,167

    Re: Need help with formula for counting incidents with multiple criteria

    I feel that this is doing your work for you rather than helping with a formula, but attached is your originally uploaded spreadsheet with working bottom table.
    work in progress.xls

  18. #18
    Forum Contributor
    Join Date
    01-04-2013
    Location
    Arkansas
    MS-Off Ver
    Microsoft Excel 2016
    Posts
    252

    Re: Need help with formula for counting incidents with multiple criteria

    FDibbins,
    The workbook you attached has the same formula I started with in the second table. I don't think you attached the one with your formula.

    Quote Originally Posted by FDibbins View Post
    I have made a start on the 2nd table, using INDIRECT() to get the sheet name. see if you can build on what I have to give you what you want?

  19. #19
    Forum Contributor
    Join Date
    01-04-2013
    Location
    Arkansas
    MS-Off Ver
    Microsoft Excel 2016
    Posts
    252

    Re: Need help with formula for counting incidents with multiple criteria

    I am sorry. I really don't want it done for me, these formulas are way more than I know. I have learned a lot on this forum on formulas. I am a beginner, and I get "stuck" a lot. I do study the formulas and try to figure out what they are saying, that's where I learn.

    I will open this up when I get home this evening.

    Quote Originally Posted by gak67 View Post
    I feel that this is doing your work for you rather than helping with a formula, but attached is your originally uploaded spreadsheet with working bottom table.
    Attachment 314890

  20. #20
    Forum Contributor
    Join Date
    01-04-2013
    Location
    Arkansas
    MS-Off Ver
    Microsoft Excel 2016
    Posts
    252

    Re: Need help with formula for counting incidents with multiple criteria

    Thank you gak67. You did do a lot of work and I thank you for that. The last 3 years in the bottom table needed a little fixing, but it all works great now.

    Quote Originally Posted by gak67 View Post
    I feel that this is doing your work for you rather than helping with a formula, but attached is your originally uploaded spreadsheet with working bottom table.
    Attachment 314890

+ 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: 12
    Last Post: 02-10-2014, 11:59 AM
  2. Replies: 3
    Last Post: 03-01-2012, 06:17 AM
  3. Replies: 0
    Last Post: 03-23-2011, 12:15 PM
  4. [SOLVED] Counting number of incidents of a month
    By CD Web in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 10-18-2005, 06:05 PM
  5. Counting Cells with multiple criteria.One criteria supporting wild
    By Azhar Arain in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 01-12-2005, 05:06 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