Lookup this, in this column, against these dates ranges

[Cr4ig]

Morning,

This is my first post, so please bare with me, i have been tasked with creating a sheet, that needs to look up this Text, against this Range, and then between these dates, and 2 different text's/Cell against a date range can anyone tell me what the best Formula would be please, i have tried a few different ones but to no avail thank you.

Craig

 

[etaf]

that needs to look up this Text, against this Range, and then between these dates, and 2 different text's/Cell against a date range can anyone tell me what the best Formula would be please

Sorry , I dont understand the text, and the screen shot image, does not make it any clearer for me to help

Can you explain more - perhaps give expected results

I can see date cells B2 and B3
But not where those are anywhere else in the sheet
Nor can i see the Text that needs to be looked up and where ?

Sorry , not getting the request

 

[Cr4ig]

Sorry, I need to look up the specific Text in Cell A5, between the dates in B2 + B3 and count the number of achievement Points , the lookup table is called Sims.

 

[etaf]

provided A5 "Year 7" is the same format text as in your SIMS Col B
No Spaces before or after the text

Then SUMIFS()
should work
SUMIFS( SUM Range , Criteria Range1 , Criteria 1 , ......)

SIMS Achievement Points column J
But they are left justified which normally means TEXT - so they need to be change to numbers - if they are infact TEXT - use DATA menu > Text to columns to change to a number - or you may need to setup in a new column using J2*1 - copied down
Anyway assuming numbers

SUMIFS( SIMS!J2:J2000,
Range to however many rows you have in the table
That adds up the Achievement Points column J
Now we have 3 Criteria
Date . End , Start and Year
Date in SIMS column I - But need to make sure they are real dates on not just text - so in SIMS column I - change the format of the column to General - all the dates should change to just a number , if not then they are text and need to be change to a real date - again using TEXT to COLUMNS
B2 and B3 are the dates to test

SUMIFS( SIMS!J2:J2000, SIMS!I2:I2000 , ">="&B2, SIMS!I2:I2000 , "<="&B3,
So that covers the dates
Now the year is in SIMS column B

SUMIFS( SIMS!J2:J2000, SIMS!I2:I2000 , ">="&B2, SIMS!I2:I2000 , "<="&B3, SIMS!B2:B2000, A5)

Now to fix the ranges and cells - we use $

To copy down Column B5 for different years

SUMIFS( SIMS!$J$2:$J$2000, SIMS!$I$2:$I$2000 , ">="&$B$2, SIMS!$I$2:$I$2000 , "<="&$B$3, SIMS!$B$2:$B$2000, $A5)
Not A5 is just $A5 so the 5 will change as you copy down.

But a lot of assumptions and factors
Maybe add the spreadsheets with XL2BB add in - see menu or my signature
OR put the file onto a share like dropbox or onedrive

I guess you also want to populate the MALE & FEMALE tables

But you dont have Y7 (B) or Y7 (G)

BUT you have Female , male in G4 & B4 And Gender in Column D
But not Y7 etc

You could use the year in A5 in those other tables
So again
SUMIFS( SIMS!$J$2:$J$2000, SIMS!$I$2:$I$2000 , ">="&$B$2, SIMS!$I$2:$I$2000 , "<="&$B$3, SIMS!$B$2:$B$2000, $A5)
Will pull the data - BUT now we need MALE
SUMIFS( SIMS!$J$2:$J$2000, SIMS!$I$2:$I$2000 , ">="&$B$2, SIMS!$I$2:$I$2000 , "<="&$B$3, SIMS!$B$2:$B$2000, $A5 , SIMS!$D$2:$D$2000, $B$4)
Will pull the data - BUT now we need FEMALE
SUMIFS( SIMS!$J$2:$J$2000, SIMS!$I$2:$I$2000 , ">="&$B$2, SIMS!$I$2:$I$2000 , "<="&$B$3, SIMS!$B$2:$B$2000, $A5 , SIMS!$D$2:$D$2000, $G$4)

 

[Cr4ig]

Your amazing thank you, if you could add on the extra lookup for Male and Female that would be great, thank you so much, i have a lot to learn.

Regards,

Craig

 

[etaf]

if you could add on the extra lookup for Male and Female

I did add, but was in an edit with in 10mins , so you may have missed
here it is again

I guess you also want to populate the MALE & FEMALE tables

But you dont have Y7 (B) or Y7 (G)

BUT you have Female , male in G4 & B4 And Gender in Column D
But not Y7 etc

You could use the year in A5 in those other tables
So again
SUMIFS( SIMS!$J$2:$J$2000, SIMS!$I$2:$I$2000 , ">="&$B$2, SIMS!$I$2:$I$2000 , "<="&$B$3, SIMS!$B$2:$B$2000, $A5)

Will pull the data - BUT now we need MALE
SUMIFS( SIMS!$J$2:$J$2000, SIMS!$I$2:$I$2000 , ">="&$B$2, SIMS!$I$2:$I$2000 , "<="&$B$3, SIMS!$B$2:$B$2000, $A5 , SIMS!$D$2:$D$2000, $B$4)

Will pull the data - BUT now we need FEMALE
SUMIFS( SIMS!$J$2:$J$2000, SIMS!$I$2:$I$2000 , ">="&$B$2, SIMS!$I$2:$I$2000 , "<="&$B$3, SIMS!$B$2:$B$2000, $A5 , SIMS!$D$2:$D$2000, $G$4)

 

[Cr4ig]

Thank you, i have completed my sheet with your help

Craig

 

[etaf]

excellent , you are welcome