Complex - Multiple Criteria

D

dunmore83

Well-known Member
Joined
Aug 24, 2011
Messages
540

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,0311,0321,0331,0341,0351,0361,0371,038
62112Heifer98212349040565410.08.1210-Aug-12690691692693694695696697
7272Heifer98212349042566827.08.1227-Aug-12673674675676677678679680
82282Heifer98212349042575728.09.1228-Sep-12641642643644645646647648
93007Heifer98212349042517624.01.1324-Jan-13523524525526527528529530
103008Heifer98212349041793224.01.1324-Jan-13523524525526527528529530
113018Heifer98212349042585829.01.1329-Jan-13518519520521522523524525
123026Heifer98212349884756131.01.1331-Jan-13516517518519520521522523
133040Heifer98212349044906306.02.1306-Feb-13510511512513514515516517
143042Heifer98212349041773706.02.1306-Feb-13510511512513514515516517
153053Heifer98212349040556007.02.1307-Feb-13509510511512513514515516
163052Heifer98212349044912507.02.1307-Feb-13509510511512513514515516
173051Heifer98212348946245407.02.1307-Feb-13509510511512513514515516
183055Heifer98212349042596708.02.1308-Feb-13508509510511512513514515
193054Heifer98212349042584808.02.1308-Feb-13508509510511512513514515
203064Heifer98212349041751511.02.1311-Feb-13505506507508509510511512
213061Heifer98212349041970811.02.1311-Feb-13505506507508509510511512
223065Heifer98212349884768512.02.1312-Feb-13504505506507508509510511
233069Heifer98209101630871614.02.1314-Feb-13502503504505506507508509
243071Heifer98212349044781914.02.1314-Feb-13502503504505506507508509
253074Heifer98212349042503917.02.1317-Feb-13499500501502503504505506
263084Heifer98212349022669126.02.1326-Feb-13490491492493494495496497
273088Heifer98212349042514028.02.1328-Feb-13488489490491492493494495
283090Heifer98212348946404203.03.1303-Mar-13485486487488489490491492
293098Heifer98212348945935611.03.1311-Mar-13477478479480481482483484
303104Heifer98212348946636001.04.1301-Apr-13456457458459460461462463
313161Heifer98212349022404109.08.1309-Aug-13326327328329330331332333
323176Heifer98200019176311915.08.1315-Aug-13320321322323324325326327
333179Heifer98200019180582116.08.1316-Aug-13319320321322323324325326
343190Heifer98200019172751218.08.1318-Aug-13317318319320321322323324
353186Heifer98212349885183918.08.1318-Aug-13317318319320321322323324
363191Heifer98200019173670919.08.1319-Aug-13316317318319320321322323
ages-excel-forum
 

Excel Facts

Spell Check in Excel
Press F7 to start spell check in Excel. Be careful, by default, Excel does not check Capitalized Werds (whoops)
Sort by date Sort by votes

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
 
Upvote 0
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!!!
 
Upvote 0
Hi, don't you just need to count those whose Birth Month & Year match the Month & Year in B10-B21?
 
Upvote 0
Ah think i got it, results like below? (I changed some data as 2nd table didn't have relevant Birth M/Y)

Code: 
[TABLE="width: 361"]
<colgroup><col><col><col><col span="2"><col><col></colgroup><tbody>[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]10-Feb-13[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]10-Aug-13[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]Feb-13[/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]28[/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]8[/TD]
[/TR]
[TR]
[TD="align: right"]Mar-13[/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]31[/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]2[/TD]
[/TR]
[TR]
[TD="align: right"]Aug-13[/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]31[/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]1[/TD]
[/TR]
[TR]
[TD="align: right"]Jul-14[/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]31[/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]0[/TD]
[/TR]
[TR]
[TD="align: right"]Sep-14[/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]30[/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]0[/TD]
[/TR]
[TR]
[TD="align: right"]Oct-14[/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]31[/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]0[/TD]
[/TR]
[TR]
[TD="align: right"]Nov-14[/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]30[/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]0[/TD]
[/TR]
[TR]
[TD="align: right"]Dec-14[/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]31[/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]0[/TD]
[/TR]
[TR]
[TD="align: right"]Jan-15[/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]31[/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]0[/TD]
[/TR]
[TR]
[TD="align: right"]Apr-15[/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]30[/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]0[/TD]
[/TR]
[TR]
[TD="align: right"]May-15[/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]31[/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]0[/TD]
[/TR]
[TR]
[TD="align: right"]Jun-15[/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]30[/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]0[/TD]
[/TR]
</tbody>[/TABLE]
 
Upvote 0
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
 
Upvote 0
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))
 
Upvote 0
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
 
Upvote 0
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.
 
Upvote 0

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-14145146147148149150151152153154
64109Heifer98209101484087719.04.1419-Apr-14144145146147148149150151152153
74110Heifer98212500093458519.04.1419-Apr-14144145146147148149150151152153
84113Heifer98209101484071020.04.1420-Apr-14143144145146147148149150151152
94114Heifer98209101482037822.04.1422-Apr-14141142143144145146147148149150
104115Heifer98209101630652704.09.1404-Sep-146789101112131415
114116Heifer98209101630896810.09.1410-Sep-140123456789
124117Heifer98209101630500611.09.1411-Sep-140012345678
134118Heifer98209101630428613.09.1413-Sep-140000123456
144119Heifer98209101630681114.09.1414-Sep-140000012345
154121Heifer98209101630854617.09.1417-Sep-140000000012
164122Heifer98209101629006017.09.1417-Sep-140000000012
174123Heifer98209101630710819.09.1419-Sep-140000000000
184125Heifer98209101630628621.09.1421-Sep-140000000000
Sheet1-forum
 
Upvote 0
You must log in or register to reply here.

Similar threads

G
  • Question Question
Copy some specific columns whose header is merge
2 3
Replies
26
Views
644
Joe4
Joe4
D
  • Locked
  • Question Question
Could you please write a code for the following scenario?
Replies
1
Views
172
Fluff
Fluff
J
  • Question Question
adding cells based on criteria across several columns
Replies
5
Views
848
Cubist
Cubist
V
  • Question Question
SUM XLOOKUP
Replies
5
Views
239
StephenCrump
S
nburaq
  • Solved
Conditional Formatting last n rows as data entered
Replies
6
Views
337
Fluff
Fluff

Forum statistics

Threads
1,223,240
Messages
6,170,951
Members
452,368
Latest member
jayp2104

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top