1

This is the formula I am currently using:

=IFS(OR($C28="Sergeant First Class",$C28="Master Sergeant",$C28="First Sergeant",$C28="Warrant Officer",$C28="Junior Lieutenant"),AND(COUNTIFS('TRYOUT LOG'!$S:$S,$D28, 'TRYOUT LOG'!$D:$D, "TRUE",'TRYOUT LOG'!$T:$T,$C28),COUNTIFS('TRYOUT LOG'!$H:$H,$D28, 'TRYOUT LOG'!$D:$D, "TRUE",'TRYOUT LOG'!$I:$I,$C28)))

This returns "TRUE", but will return "FALSE" if the criteria isn't met. However, I want it to actually count the amount of times that the criteria is met, rather than just returning TRUE/FALSE.

I'll try to explain what exactly I want the formula to do.

I am currently checking if cell in C28 is a certain text, if they are then to count how many times this criteria appears in the 'TRYOUT LOG' sheet. I want it to check that column S on the Tryout Log matches D28, then for column D on the Tryout Log to match to "TRUE" and then for column T on the Tryout Log to match C28, then count how many that is all correct, and then ALSO (the AND statement) count how many times it appears in the other rows too, matching a similar criteria with just slightly different columns.

Essentially I need it to just check if a cell is = to a certain text, then count how many times 2 similar criterias are matched on the TRYOUT LOG sheet and return that amount.

Hopefully that all makes sense, if not I'm happy to answer any questions.

Any help is appreciated so thank you in advance.

Edited to include viewable link to the sheet https://docs.google.com/spreadsheets/d/11-KJOgAZZvTO481wbqL1VR8hoSR02vCj-kHrJ619yVM/edit?usp=sharing

Louis
  • 11
  • 2
  • Yes, keep in mind that we can't see what you are seeing. So while your post and your goal are clear to *you* who can see everything and has been working on this, those of us "out here" have no reference points. As player0 invited, please share a link to the spreadsheet (or to a copy of it, or to a copy with confidential information replaced with dummy data), so that we can all see what you are seeing *along with a few examples of the results you expect a formula to produce*, hand-entered by you. – Erik Tyler Apr 07 '22 at 04:02
  • @player0 Sorry, should've done that initially. I've included on the Sheet what I'm expecting, and you'll see the actual result I get. Probably some stupid mistake I've missed or something obvious, but please let me know. Below is a link to the document with view access. If you need edit access let me know :). https://docs.google.com/spreadsheets/d/11-KJOgAZZvTO481wbqL1VR8hoSR02vCj-kHrJ619yVM/edit?usp=sharing – Louis Apr 07 '22 at 16:00

0 Answers0