0

I have a sheet in my workbook called Students_Record. On that sheet, I am entering a formula that refers to columns of cells (ClassDate and StudentNumber) from another sheet called Class_Record.

I want it to look through the data in these two columns. I want to find the MAX value in the ClassDate column when the StudentNumber is 1, 2, 3, etc.

Hayley Jonasson
  • 3
  • 2
  • 2
    Try using MAXIFS. – BigBen Jan 31 '22 at 17:58
  • 1
    ie: https://stackoverflow.com/questions/53014575/excel-formula-to-return-value-from-another-column-if-there-are-cells-matching-in – Scott Craner Jan 31 '22 at 18:00
  • I have entered the following formula to search for StudentNumber 1. =MAXIFS('Class_Record'!B2:B3392,'Class_Record'!H2:H3392="1") It is telling me that I have entered too few arguments for this function. – Hayley Jonasson Jan 31 '22 at 18:01
  • 1
    `=MAXIFS('Class_Record'!B2:B3392,'Class_Record'!H2:H3392,1)` – Scott Craner Jan 31 '22 at 18:03
  • Sorry. I am new to using array formulas and functions in Excel. I try entering the formula in the bar and then clicking Ctrl+Shift+Enter and it gives me a popup for updating values and selecting a file in my computer. I think I'm doing something wrong. – Hayley Jonasson Jan 31 '22 at 18:05
  • You have a typo in the sheet name. You also do not need Ctrl-Shift-Enter with MAXIFS. – Scott Craner Jan 31 '22 at 18:08
  • You need to type the formula, instead of copying it from here, that is the reason why it gives you a pop up, please type the formula, manually as provided by @ScottCraner sir ! – Mayukh Bhattacharya Jan 31 '22 at 18:09
  • I'm not sure what you mean. I have an error in my post regarding the worker's dashboard. The two sheets are called Class_Record and Students_Record. I still get the popup when I just use Enter to enter the formula in the cell. – Hayley Jonasson Jan 31 '22 at 18:11
  • @Mayukh Bhattacharya I still get the popup when I enter the formula manually. – Hayley Jonasson Jan 31 '22 at 18:13
  • 1
    Somehow the sheet names in the formula do not match the actual sheet name. There may be non printable characters in one or the other. Check the actual names and make sure there is not a space or other non printable character that is not represented in the formula – Scott Craner Jan 31 '22 at 18:14
  • @ScottCraner sir, absolutely, there should be something a typo or a non printable character, thats the reason why i asked to manually type the formula that should work! – Mayukh Bhattacharya Jan 31 '22 at 18:16
  • You can view my workbook here. [link](http://filedump.to/download/18e11b4681864f438f84a4b1a2cfd4a5/Work%20Records.xlsx) – Hayley Jonasson Jan 31 '22 at 18:21
  • @HayleyJonasson that link doesn't show up anything ! – Mayukh Bhattacharya Jan 31 '22 at 18:26
  • @Mayukh Bhattacharya Try this one: [link](https://file.io/0knyHjaYqcGP) – Hayley Jonasson Jan 31 '22 at 18:31
  • So the formula provided by @ScottCraner Sir, is working. Please convert the dates as actual excel dates, those are in text format, so it was not working `=MAXIFS('Class Record'!B$2:B$3392,'Class Record'!$H$2:$H$3392,1)' – Mayukh Bhattacharya Jan 31 '22 at 18:54
  • 1
    @HayleyJonasson So it appears that you put `_` where the sheet name has a space. `_` does not equal a space. – Scott Craner Jan 31 '22 at 18:56
  • @ScottCraner sir, the dates are not actual excel dates, those are formatted as text, also what you pointed its another reason as well. – Mayukh Bhattacharya Jan 31 '22 at 18:57
  • 1
    @MayukhBhattacharya I saw your comment. I do not download files from unknown people. Glad you were able to help. – Scott Craner Jan 31 '22 at 18:58
  • @ScottCraner thanks a million Sir, but how do i share a link here! – Mayukh Bhattacharya Jan 31 '22 at 19:00
  • 1
    @MayukhBhattacharya you can [edit] your post and use any file uploader link. But realize we feel that the question should stand on its own, without the need of downloading files. You can attach photos of the sheets and its make up. Most will not download files from unknown individuals. – Scott Craner Jan 31 '22 at 19:02
  • @ScottCraner Sir, alright, i am very much new to this, i am not sharing any links neither downloading it as well. Thank you, However the solution is already provided OP, can use it! – Mayukh Bhattacharya Jan 31 '22 at 19:05
  • Let us [continue this discussion in chat](https://chat.stackoverflow.com/rooms/241585/discussion-between-mayukh-bhattacharya-and-scott-craner). – Mayukh Bhattacharya Jan 31 '22 at 19:05
  • I have changed the sheet name to Class_Record and changed the format of the entries in the ClassDate column so that they are in the Date/Time format (not text) but it is just showing a generic 1900-01-00. – Hayley Jonasson Jan 31 '22 at 19:07
  • @HayleyJonasson simply changing the format of a cell does not change the date/time from text to an actual date. The items are still text that looks like a date. you will need to parse the string to create a true date. There are many examples how to do that with formula, vba, or even Text to Columns. – Scott Craner Jan 31 '22 at 19:09
  • 1
    @MayukhBhattacharya sorry I thought I was talking to the OP. – Scott Craner Jan 31 '22 at 19:09
  • @ScottCraner It is working now. Thank you! – Hayley Jonasson Jan 31 '22 at 19:14
  • @HayleyJonasson what i did, i copied the 1 in cell A2 sheet Class Record, selected the range B2:B3392, press ALT H V S --> Paste Values --> Operation --> Multiply --> Ok, The Dates changes to numbers and then press CTRL 1 --> Format Cells --> Number Tab --> Category --> Date --> Ok, Now use the formula as provided --> `=MAXIFS('Class Record'!B$2:B$3392,'Class Record'!$H$2:$H$3392,1)` – Mayukh Bhattacharya Jan 31 '22 at 19:16

0 Answers0