Nesting a formula

N

Newbienew

Active Member
Joined
Mar 17, 2017
Messages
376
Office Version
  1. 2016
Platform
  1. Windows
I am currently looking to nest a already formula to be combined with one that will count the number of entries in between to dates.
Book1
ABC
1titleratingcount between dates.
2aa3 lole4447
3aa2 lole58
4aa1 lole69
5aac lole777
6aja1 lole64
7aac lole72
8aa3 lole444
9aa3 lole475
10aa3 lole422
11aa3 lole455
12aa3 lole42
13aa3 lole447
14aa3 lole412
15aa3 lole42
16aa3 lole447
17aa3 lole40
Sheet1
Cell Formulas
RangeFormula
B2:B17B2=IFERROR(IF(A2<>"","e"&IF(RIGHT(LEFT(A2,SEARCH(" ",A2)-1),1)="1","6",IF(RIGHT(LEFT(A2,SEARCH(" ",A2)-1),1)="2","5",IF(RIGHT(LEFT(A2,SEARCH(" ",A2)-1),1)="3","4",IF(RIGHT(LEFT(A2,SEARCH(" ",A2)-1),1)="c","7")))),""),"input error")


My original plan was to do a countif based off the title and name (Column A) and have it count how many entires they had between two dates. However my issue is if the number goes up goes up the dates for that level need to auto change. as the title and ranking are linked. The ranking has its own dates criteria. So im just not sure how to make this happen. Please help
 

Excel Facts

Add Bullets to Range
Select range. Press Ctrl+1. On Number tab, choose Custom. Type Alt+7 then space then @ sign (using 7 on numeric keypad)
Sort by date Sort by votes
Count between what dates? There is nothing date related visible in your example.
 
Upvote 0
I have added dates as well as the example. I also attempted to try some formulas which i added.

Book1
ABCDE
1titleratingcount between dates. Formula Attempts
2
3aa3 Jamese44472/2/2018402
4aa2 Frankye582/2/2020 
5aa1 Wynne692/4/2019#VALUE!
6aac Jaye7772/5/2019
7aja1 Helene646/6/2020
8aac Wallye722/7/2019
9aa2 Frankye5442/8/2019
10aa1 Wynne6752/9/2019
11aa2 Frankye5222/2/2019
12aa2 Frankye5552/11/2019
13aa2 Frankye522/12/2019
14aa2 Frankye5472/13/2019
15aa2 Frankye5122/14/2019
16aja1 Helene624/15/2019
17aja1 Helene6474/16/2019
18aja1 Helene605/17/2019
Sheet1
Cell Formulas
RangeFormula
E3E3=SUMIFS($C$3:$C$18,$D$3:$D$18,">="&"2/2/19",$D$3:$D$18,"<="&"2/2/20")
E4E4=IF(A4="E6",SUMIFS($C$3:$C$18,$D$3:$D$18,">="&"2/2/19",$D$3:$D$18,"<="&"2/2/20"),IF(A4="e5",SUMIFS($C$3:$C$18,$D$3:$D$18,">="&"2/217",$D$3:$D$18,"<="&"2/2/18"),IF(A4="E4",SUMIFS($C$3:$C$18,$D$3:$D$18,">="&"2/2/19",$D$3:$D$18,"<="&"2/2/16"),"")))
E5E5=COUNTIFS(A3:A18,D2,B3:D18,IF(B3="E4",SUMIFS($C$3:$C$18,$D$3:$D$18,">="&"2/2/19",$D$3:$D$18,"<="&"2/2/20"),IF(B3="e5",SUMIFS($C$3:$C$18,$D$3:$D$18,">="&"2/217",$D$3:$D$18,"<="&"2/2/18"),IF(B3="E6",SUMIFS($C$3:$C$18,$D$3:$D$18,">="&"2/2/19",$D$3:$D$18,"<="&"2/2/16"),""))))
B3:B18B3=IFERROR(IF(A3<>"","e"&IF(RIGHT(LEFT(A3,SEARCH(" ",A3)-1),1)="1","6",IF(RIGHT(LEFT(A3,SEARCH(" ",A3)-1),1)="2","5",IF(RIGHT(LEFT(A3,SEARCH(" ",A3)-1),1)="3","4",IF(RIGHT(LEFT(A3,SEARCH(" ",A3)-1),1)="c","7")))),""),"input error")
 
Upvote 0
Is this what you need?

=IF(B3="E6",SUMIFS($C$3:$C$18,$D$3:$D$18,">="&"2/2/19",$D$3:$D$18,"<="&"2/2/20"),IF(B3="e5",SUMIFS($C$3:$C$18,$D$3:$D$18,">="&"2/2/17",$D$3:$D$18,"<="&"2/2/18"),IF(B3="E4",SUMIFS($C$3:$C$18,$D$3:$D$18,">="&"2/2/19",$D$3:$D$18,"<="&"2/2/16"),"")))

If not, please calculate the expected results for your example and enter them manually.
 
Upvote 0
The formula I am trying to make will check the title names count up how many entry dates there are between dates. But each title is connected to a rating. And the rating has different date ranges.

So if aja1 helen had entries from jan to feb I would need to calculate how many times here name appeared within time frame, sum up the dates between Jan 1 and Feb 28 for her rating. So if i change her title to aja2 it will automatically change the to those dates and change her numbers. All in one formula.
 
Upvote 0
Your example is confusing, based on your description I'm going with this method. Note that I've used different dates as the ones in your formula made no sense, you had start dates later than end dates. Also, I had to reformat the dates in column D to fit with my regional settings, you will need to use your original dates for the formula to work on your pc.

Book2
ABCDE
1titleratingcount between dates. Formula Attempts
2
3aa3 Jamese444702/02/2018447
4aa2 Frankye5802/02/202022
5aa1 Wynne6904/02/201984
6aac Jaye77705/02/201977
7aja1 Helene6406/06/202049
8aac Wallye7207/02/20192
9aa2 Frankye54408/02/201922
10aa1 Wynne67509/02/201984
11aa2 Frankye52202/02/201922
12aa2 Frankye55511/02/201922
13aa2 Frankye5212/02/201922
14aa2 Frankye54713/02/201922
15aa2 Frankye51214/02/201922
16aja1 Helene6215/04/201949
17aja1 Helene64716/04/201949
18aja1 Helene6017/05/201949
Sheet3
Cell Formulas
RangeFormula
B3:B18B3=IFERROR(IF(A3<>"","e"&IF(RIGHT(LEFT(A3,SEARCH(" ",A3)-1),1)="1","6",IF(RIGHT(LEFT(A3,SEARCH(" ",A3)-1),1)="2","5",IF(RIGHT(LEFT(A3,SEARCH(" ",A3)-1),1)="3","4",IF(RIGHT(LEFT(A3,SEARCH(" ",A3)-1),1)="c","7")))),""),"input error")
E3:E18E3=SUMIFS($C$3:$C$18,$A$3:$A$18,A3,$D$3:$D$18,">="&CHOOSE(MATCH(B3,"E"&{4,5,6,7},0),"2/2/16","2/2/17","2/2/18","2/2/19"),$D$3:$D$18,"<="&CHOOSE(MATCH(B3,"E"&{4,5,6,7},0),"2/2/18","2/2/19","2/2/20","2/2/21"))
 
Upvote 0
This is what I am attempting. Yes, sorry I not good at explaining over text. I am more of a visual explainer, face to face. But this is exactly what I was attempting. After looking at your formula, I would need to be able to updates in for each rating E4-E7. That's whyy the dates are sporadic. So E4 maybe between Mar19 and FEB20 dates,E5 might be MAY18 and APR20 but i would need the means to change it. I was thinking of using F1 thru I1 for the start date and F2 thru I1 for the end dates. So F1 would be for E4 and so on. If this makes sense. Every year I would have to change the dates or I may have to isolate one persons work for a duration of time.

Above anything THANK YOU VERY MUCH FOR YOUR PATIENCE AND HELP
 
Upvote 0
Using columns F:I as you have said, I would suggest entering the ratings in F1:I1, start dates in F2:I2 and end dates in F3:I3, then use the following formula in E3.

=SUMIFS($C$3:$C$18,$A$3:$A$18,A3,$D$3:$D$18,">="&HLOOKUP(B3,$F$1:$I$2,2,0),$D$3:$D$18,"<="&HLOOKUP(B3,$F$1:$I$3,3,0))

I think that I have that right but haven't tested it.
 
Upvote 0
This is what I got when I put in.
Book1
ABCDEFGHI
1titleratingentry put indate1/1/20184/1/20187/1/20182/1/2018
212/31/20183/31/20196/31/20191/31/2019
3aa3 JamesE44472/2/20180
4aa2 FrankyE582/2/20200
5aa1 WynnE692/4/20190
6aac JayE7772/5/20190
7aja1 HelenE646/6/20190
8aac WallyE722/7/20190
9aa2 FrankyE5442/8/20190
10aa1 WynnE6752/9/20190
Sheet1
Cell Formulas
RangeFormula
B3:B10B3=IFERROR(IF(A3<>"","E"&IF(RIGHT(LEFT(A3,SEARCH(" ",A3)-1),1)="1","6",IF(RIGHT(LEFT(A3,SEARCH(" ",A3)-1),1)="2","5",IF(RIGHT(LEFT(A3,SEARCH(" ",A3)-1),1)="3","4",IF(RIGHT(LEFT(A3,SEARCH(" ",A3)-1),1)="c","7")))),""),"input error")
E3:E10E3=SUMIFS($C$3:$C$18,$A$3:$A$18,A3,$D$3:$D$18,">="&HLOOKUP(B3,$F$1:$I$2,2,0),$D$3:$D$18,"<="&HLOOKUP(B3,$F$1:$I$3,3,0))


Do you think its a good idea to use a reference cell for e4-e7? like this.

Book1
ABCDEFGHI
1E4E5E6E7
2titleratingentry put indate1/1/20184/1/20187/1/20182/1/2018
312/31/20183/31/20196/31/20191/31/2019
4aa3 JamesE44472/2/20180
5aa2 FrankyE582/2/20200
6aa1 WynnE692/4/20190
7aac JayE7772/5/20190
8aja1 HelenE646/6/20190
9aac WallyE722/7/20190
10aa2 FrankyE5442/8/20190
11aa1 WynnE6752/9/20190
12aa2 FrankyE5222/2/20190
Sheet1
Cell Formulas
RangeFormula
B4:B12B4=IFERROR(IF(A4<>"","E"&IF(RIGHT(LEFT(A4,SEARCH(" ",A4)-1),1)="1","6",IF(RIGHT(LEFT(A4,SEARCH(" ",A4)-1),1)="2","5",IF(RIGHT(LEFT(A4,SEARCH(" ",A4)-1),1)="3","4",IF(RIGHT(LEFT(A4,SEARCH(" ",A4)-1),1)="c","7")))),""),"input error")
E4:E12E4=SUMIFS($C$4:$C$19,$A$4:$A$19,A4,$D$4:$D$19,">="&HLOOKUP(B4,$F$2:$I$3,2,0),$D$4:$D$19,"<="&HLOOKUP(B4,$F$2:$I$4,3,0))
 
Upvote 0
That was what I suggested, it doesn't work if you insert a row at the top after entering the formulas.
Book1
ABCDEFGHI
1E4E5E6E7
2titleratingentry put indate01/01/201801/04/201801/07/201801/02/2018
331/12/201831/03/201930/06/201931/01/2019
4aa3 JamesE444702/02/2018447
5aa2 FrankyE5802/02/202066
6aa1 WynnE6904/02/201984
7aac JayE77705/02/20190
8aja1 HelenE6406/06/20194
9aac WallyE7207/02/20190
10aa2 FrankyE54408/02/201966
11aa1 WynnE67509/02/201984
12aa2 FrankyE52202/02/201966
Sheet4
Cell Formulas
RangeFormula
B4:B12B4=IFERROR(IF(A4<>"","E"&SEARCH(MID(A4,SEARCH(" ",A4)-1,1),"321C")+3,""),"Input Error")
E4:E12E4=SUMIFS($C$4:$C$19,$A$4:$A$19,A4,$D$4:$D$19,">="&HLOOKUP(B4,$F$1:$I$3,2,0),$D$4:$D$19,"<="&HLOOKUP(B4,$F$1:$I$3,3,0))
 
Last edited:
Upvote 0
You must log in or register to reply here.

Similar threads

J
  • Solved
Nested LOOKUP formula with TRIM and FIND
Replies
2
Views
120
janema
J
N
  • Solved
Function to find a portion of text in a cell
Replies
5
Views
487
Fluff
Fluff
SaraWitch
  • Solved
Calculate number of days between two dates when one is created by another formula
Replies
2
Views
392
SaraWitch
SaraWitch
Z
  • Question
Massive Nested IF Statement Help Needed
Replies
1
Views
120
Zutalores
Z
A
  • Question
Counting hours spent ( within certain shift hours) between two dates.
Replies
1
Views
52
eiloken
E

Forum statistics

Threads
1,214,987
Messages
6,122,613
Members
449,090
Latest member
vivek chauhan

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