Find if based on selected criteria

[nostradamus]

Hello all,

Have four formula requests based on certain criteria from the following dataset (using Office 365).
Formulas for Cells E2 - E5, for Low category ("1):
Cell E 2: Total count of "1" in range (B2:B14), if not, return 0
Cell E 3: Max value from Col A associated with "1" from Col B in range (A1:A14)
Cell E 4: Max continuous count of "1" in range (B2:B14), if not, return 0
Cell E 5: Max value from Col A associated with "1" from Col B in range (A1:A14) - only from the continuous count

Similar formulas in Cells E7 - E10, but with the Medium category ("2").

Hope formulas in E3 and E5 make sense, I could have worded it better ...
Thanks!!!

Copy of Colorado at Bastrop 1985-2021.xls
	A	B	C	D	E
1	Quantity	Category		Variable	Formula Values
2	625	1		Low-Count	7
3	616	1		Low-maxQtyForLow	1030
4	578	1		Low-continuous count	5
5	571	1		Low-maxQtyForContinuousCount	625
6	571	1
7	6030	2		Medium-Count	6
8	2430	2		Medium-maxQtyForMedium	6030
9	1030	1		Medium-continuous count	4
10	920	1		Medium-maxQtyForContinuousCount	2920
11	2920	2
12	2750	2
13	2690	2
14	2540	2
15
16
Sheet2

 

[nostradamus]

I came with a formula for
E2 = COUNTIF(B2:B14,"=1")
E4 =MAX(FREQUENCY(IF(B2:B14=1,ROW(B2:B14)),IF(B2:B14<>1,ROW(B2:B22))))

Can someone please check it, and also help with the other formulas.

 

[Fluff]

Cross-posting (posting the same question in more than one forum) is not against our rules, but the method of doing so is covered by #13 of the Forum Rules.

Be sure to follow & read the link at the end of the rule too!

Cross posted at: Count continuous instances and get max value from corresponding column
If you have posted the question at more places, please provide links to those as well.

If you do cross-post in the future and also provide links, then there shouldn’t be a problem.

 

[Fluff]

Both those formulae look fine, other than you have different sized ranges in the E4 formula. For E3 you can use

=MAXIFS(A:A,B:B,1)

 

[nostradamus]

Thanks for the cross-post link, yes I did post on the other forum

I still need help with formula in E5, not sure if I need to fill in more details on my desired outcome.

 

[Fluff]

Unfortunately the E5 formula is beyond my knowledge, which is why I didn't reply yesterday.

 

[nostradamus]

Found solution to questions from the cross-post website.
Thanks for your help with some of the formulas.

 

[Fluff]

Glad you got it sorted & thanks for letting us now.