Sumif, Countif, Maxif ... with a YES qualifier

[ellison]

Hi, we are trying to use the sumif, countif & maxif functions, but with an extra criteria to qualify for the counts = "y"
Hopefully the sheet below may show what we mean a little better!
In other words, we are trying to do different counts for the types of info (Alpha and Beta) depending on if each row has a corresponding "y" in the "Qualifier" column.
Also trying to find best way of showing which actual row of info has the "maxif" for each qualifying info
NB there is always an entry in the info column. But the qty column is sometimes blank. As for example on the entries for "Charlie" in the last few rows

sumif-countif-maxif-with-a-YES-qualifier-question.xlsx
	A	B	C	D	E	F	G	H
1	Row	Info	Amt	Qualifier	Sumif "y"	Countif "y"	Maxif "y"	Maxif (but only on the appropriate row)
2	2	Alpha	100	y	500	3	225
3	3	Alpha	150		500	3	225
4	4	Alpha	175	y	500	3	225
5	5	Alpha			500	3	225
6	6	Alpha	225	y	500	3	225	225
7	7	Alpha	25		500	3	175
8	8	Beta	500		225	2	175
9	9	Beta	60		225	2	175
10	10	Beta	175	y	225	2	175	175
11	11	Beta			225	2	175
12	12	Beta	50	y	225	2	175
13	13	Charlie			0	0	0	No Max
14	14	Charlie			0	0	0	No Max
15	15	Charlie			0	0	0	No Max
Sheet1

Huge thanks to anybody for taking a look!

 

[Fluff]

For the sumifs use

=SUMIFS(C:C,B:B,B2,D:D,"y")

and similar for the countifs.
For the max use

=LET(m,MAXIFS(C:C,B:B,B2,D:D,"y"),IF(m=0,"No max",IF(C2=m,m,"")))

 

[DanteAmor]

Try:

Dante Amor
	A	B	C	D	E	F	G	H
1	Row	Info	Amt	Qualifier	Sumif "y"	Countif "y"	Maxif "y"	Maxif (but only on the appropriate row)
2	2	Alpha	100	y	500	3	225
3	3	Alpha	150		500	3	225
4	4	Alpha	175	y	500	3	225
5	5	Alpha			500	3	225
6	6	Alpha	225	y	500	3	225	225
7	7	Alpha	25		500	3	175
8	8	Beta	500		225	2	175
9	9	Beta	60		225	2	175
10	10	Beta	175	y	225	2	175	175
11	11	Beta			225	2	175
12	12	Beta	50	y	225	2	175
13	13	Charlie			0	0	0	No Max
14	14	Charlie			0	0	0	No Max
15	15	Charlie			0	0	0	No Max
Hoja4

Cell Formulas
Range	Formula
E2:E15	E2	=SUMIFS($C$2:$C$15,$B$2:$B$15,B2,$D$2:$D$15,"y")
F2:F15	F2	=COUNTIFS($B$2:$B$15,B2,$D$2:$D$15,"y")
H2:H15	H2	=IF(G2=0,"No Max",IF(C2=G2,G2,""))

For the MAXIF:

=MAXIFS($C$2:$C$15,$B$2:$B$15,B2,$D$2:$D$15,"y")

 

[ellison]

Absolutely brilliant Mr Fluff....
"Sumif" all working, "maxif" all working and "maxif but only on the right line" all working.

Just trying to get the countifs working
I tried this below, but an error message popped up: "you've entered too few arguments for this function"

=COUNTFS(C:C,B:B,B2,D:D,"y")

 

[ellison]

All sorted now, with huge thanks!

I used Mr Fluff's solution for the sumif, maxif and maxif but only on the appropriate row.

And Dante's suggestion on the countif:

=COUNTIFS(B:B,B2,D:D,"y")

Thanks again one and all!!!!

 

[Fluff]

Glad we could help & thanks for the feedback.