Thanks Thanks:  0
Likes Likes:  0
Page 1 of 20 12311 ... LastLast
Results 1 to 10 of 193

Thread: Complex - Multiple Criteria

  1. #1
    Board Regular
    Join Date
    Aug 2011
    Posts
    514
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Complex - Multiple Criteria

    Excel 2012
    BCDEFGHIJKLMNOPQ
    2Ear TagSexNLISBirth Date01-Jul-1402-Jul-1403-Jul-1404-Jul-1405-Jul-1406-Jul-1407-Jul-1408-Jul-14
    3
    4
    51658Heifer-04.09.1104-Sep-111,031 1,032 1,033 1,034 1,035 1,036 1,037 1,038
    62112Heifer98212349040565410.08.1210-Aug-12690 691 692 693 694 695 696 697
    7272Heifer98212349042566827.08.1227-Aug-12673 674 675 676 677 678 679 680
    82282Heifer98212349042575728.09.1228-Sep-12641 642 643 644 645 646 647 648
    93007Heifer98212349042517624.01.1324-Jan-13523 524 525 526 527 528 529 530
    103008Heifer98212349041793224.01.1324-Jan-13523 524 525 526 527 528 529 530
    113018Heifer98212349042585829.01.1329-Jan-13518 519 520 521 522 523 524 525
    123026Heifer98212349884756131.01.1331-Jan-13516 517 518 519 520 521 522 523
    133040Heifer98212349044906306.02.1306-Feb-13510 511 512 513 514 515 516 517
    143042Heifer98212349041773706.02.1306-Feb-13510 511 512 513 514 515 516 517
    153053Heifer98212349040556007.02.1307-Feb-13509 510 511 512 513 514 515 516
    163052Heifer98212349044912507.02.1307-Feb-13509 510 511 512 513 514 515 516
    173051Heifer98212348946245407.02.1307-Feb-13509 510 511 512 513 514 515 516
    183055Heifer98212349042596708.02.1308-Feb-13508 509 510 511 512 513 514 515
    193054Heifer98212349042584808.02.1308-Feb-13508 509 510 511 512 513 514 515
    203064Heifer98212349041751511.02.1311-Feb-13505 506 507 508 509 510 511 512
    213061Heifer98212349041970811.02.1311-Feb-13505 506 507 508 509 510 511 512
    223065Heifer98212349884768512.02.1312-Feb-13504 505 506 507 508 509 510 511
    233069Heifer98209101630871614.02.1314-Feb-13502 503 504 505 506 507 508 509
    243071Heifer98212349044781914.02.1314-Feb-13502 503 504 505 506 507 508 509
    253074Heifer98212349042503917.02.1317-Feb-13499 500 501 502 503 504 505 506
    263084Heifer98212349022669126.02.1326-Feb-13490 491 492 493 494 495 496 497
    273088Heifer98212349042514028.02.1328-Feb-13488 489 490 491 492 493 494 495
    283090Heifer98212348946404203.03.1303-Mar-13485 486 487 488 489 490 491 492
    293098Heifer98212348945935611.03.1311-Mar-13477 478 479 480 481 482 483 484
    303104Heifer98212348946636001.04.1301-Apr-13456 457 458 459 460 461 462 463
    313161Heifer98212349022404109.08.1309-Aug-13326 327 328 329 330 331 332 333
    323176Heifer98200019176311915.08.1315-Aug-13320 321 322 323 324 325 326 327
    333179Heifer98200019180582116.08.1316-Aug-13319 320 321 322 323 324 325 326
    343190Heifer98200019172751218.08.1318-Aug-13317 318 319 320 321 322 323 324
    353186Heifer98212349885183918.08.1318-Aug-13317 318 319 320 321 322 323 324
    363191Heifer98200019173670919.08.1319-Aug-13316 317 318 319 320 321 322 323

    ages-excel-forum




  2. #2
    Board Regular
    Join Date
    Aug 2011
    Posts
    514
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Complex - Multiple Criteria

    Excel 2012
    ABCDEFGHIJK
    1
    2
    3
    404-Sep-14
    5
    6
    729-Jan-15
    8
    9
    10Jul-1431121
    11Aug-1431
    12Sep-1430
    13Oct-1431
    14Nov-1430
    15Dec-1431
    16Jan-1531
    17Feb-1528
    18Mar-1531
    19Apr-1530
    20May-1531
    21Jun-1530

    Sheet3




  3. #3
    Board Regular
    Join Date
    Aug 2011
    Posts
    514
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Complex - Multiple Criteria

    I need a formula to go in column H10:H21 that searches the data in F5:F36 & returns the total number found between the 2 dates in cell H4 & H7.

    I can achieve this by using =COUNTIFS(Sheet1!$F$4:$F$659,">"&H4,Sheet1!$F$4:$F$659,"<"&H7)

    However I need the formula to also only count between those dates if that birth date in column F was born within the respective months in B10:B21. The data in J5:Q36 will be extended for a financial year & shows the age in days of each animal in column B. However there will be many births that happen after the financial year begins.

    Help greatly appreciated!!!

  4. #4
    Board Regular
    Join Date
    Apr 2003
    Location
    England
    Posts
    5,853
    Post Thanks / Like
    Mentioned
    2 Post(s)
    Tagged
    1 Thread(s)

    Default Re: Complex - Multiple Criteria

    Hi, don't you just need to count those whose Birth Month & Year match the Month & Year in B10-B21?
    To post screen shots download the MrExcel HTML Maker
    Look here for other options Recommended Add-ins and Links

  5. #5
    Board Regular
    Join Date
    Apr 2003
    Location
    England
    Posts
    5,853
    Post Thanks / Like
    Mentioned
    2 Post(s)
    Tagged
    1 Thread(s)

    Default Re: Complex - Multiple Criteria

    Ah think i got it, results like below? (I changed some data as 2nd table didn't have relevant Birth M/Y)

    Code:
    10-Feb-13
    10-Aug-13
    Feb-13 28 8
    Mar-13 31 2
    Aug-13 31 1
    Jul-14 31 0
    Sep-14 30 0
    Oct-14 31 0
    Nov-14 30 0
    Dec-14 31 0
    Jan-15 31 0
    Apr-15 30 0
    May-15 31 0
    Jun-15 30 0
    To post screen shots download the MrExcel HTML Maker
    Look here for other options Recommended Add-ins and Links

  6. #6
    Board Regular
    Join Date
    Aug 2011
    Posts
    514
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Complex - Multiple Criteria

    It's a little hard to envisage looking at those numbers but it looks correct. If you can post the formula I can give it a go

  7. #7
    Board Regular
    Join Date
    Apr 2003
    Location
    England
    Posts
    5,853
    Post Thanks / Like
    Mentioned
    2 Post(s)
    Tagged
    1 Thread(s)

    Default Re: Complex - Multiple Criteria

    Try this and let me know.....

    =SUMPRODUCT((TEXT(Sheet1!$F$4:$F$659,"mm/yy")=TEXT(B10,"mm/yy"))*(Sheet1!$F$4:$F$659>$H$4)*(Sheet1!$F$4:$F$659<$H$7))
    To post screen shots download the MrExcel HTML Maker
    Look here for other options Recommended Add-ins and Links

  8. #8
    Board Regular
    Join Date
    Aug 2011
    Posts
    514
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Complex - Multiple Criteria

    Works if dates in H4 & H7 are inside the financial year i.e. 01-Jul-14 to 30-Jun-15

    However I need it be cumulative so in your example the formula go 8 then 10 then 11

    Also if all the births are prior to the 01-Jul-14 then the formula needs to return the total number found between the min & max dates

    Thanks

  9. #9
    Board Regular
    Join Date
    Apr 2003
    Location
    England
    Posts
    5,853
    Post Thanks / Like
    Mentioned
    2 Post(s)
    Tagged
    1 Thread(s)

    Default Re: Complex - Multiple Criteria

    The cumulative is easy, but I don't follow "Also if all the births are prior to the 01-Jul-14 then the formula needs to return the total number found between the min & max dates".

    Can you post your 2nd table again, with the manually entered results that you expect.
    To post screen shots download the MrExcel HTML Maker
    Look here for other options Recommended Add-ins and Links

  10. #10
    Board Regular
    Join Date
    Aug 2011
    Posts
    514
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Complex - Multiple Criteria

    Excel 2012
    BCDEFGHIJKLMNOPQRS
    2Ear TagSexNLISBirth Date10-Sep-1411-Sep-1412-Sep-1413-Sep-1414-Sep-1415-Sep-1416-Sep-1417-Sep-1418-Sep-1419-Sep-14
    3
    4
    54108Heifer98209101485394118.04.1418-Apr-14145 146 147 148 149 150 151 152 153 154
    64109Heifer98209101484087719.04.1419-Apr-14144 145 146 147 148 149 150 151 152 153
    74110Heifer98212500093458519.04.1419-Apr-14144 145 146 147 148 149 150 151 152 153
    84113Heifer98209101484071020.04.1420-Apr-14143 144 145 146 147 148 149 150 151 152
    94114Heifer98209101482037822.04.1422-Apr-14141 142 143 144 145 146 147 148 149 150
    104115Heifer98209101630652704.09.1404-Sep-146 7 8 9 10 11 12 13 14 15
    114116Heifer98209101630896810.09.1410-Sep-140 1 2 3 4 5 6 7 8 9
    124117Heifer98209101630500611.09.1411-Sep-140 0 1 2 3 4 5 6 7 8
    134118Heifer98209101630428613.09.1413-Sep-140 0 0 0 1 2 3 4 5 6
    144119Heifer98209101630681114.09.1414-Sep-140 0 0 0 0 1 2 3 4 5
    154121Heifer98209101630854617.09.1417-Sep-140 0 0 0 0 0 0 0 1 2
    164122Heifer98209101629006017.09.1417-Sep-140 0 0 0 0 0 0 0 1 2
    174123Heifer98209101630710819.09.1419-Sep-140 0 0 0 0 0 0 0 0 0
    184125Heifer98209101630628621.09.1421-Sep-140 0 0 0 0 0 0 0 0 0

    Sheet1-forum




Some videos you may like

User Tag List

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •