COUNTIF w/ greater than & name match

[DaRTH KiRo]

I am needing to COUNTIF column B on "Data" sheet is greater than column C on "Data" sheet, if the name in column A of "Totals" sheet matches the name from column A on "Data" sheet. The Data sheet has multiple rows with the same name that need to have the values in B and C checked.

Data Sheet:

Employee	Wins	Losses
Name1	5​	2​
Name2	2​	4​
Name3	1​	8​
Name1	8​	5​
Name1	5​	1​
Name3	6​	5​

Totals Sheet:

Employee	Total
Name1
Name2
Name3

 

[kweaver]

Please update your account details to indicate which version of Excel you are running.
Different solutions to problems often depend on the version you're running.

One way:

Book1
	A	B
1	Employee	Total
2	Name1	3
3	Name2	0
4	Name3	1
Totals

Cell Formulas
Range	Formula
B2:B4	B2	=SUMPRODUCT((Data!A2:A7=A2)*(Data!B2:B7>Data!C2:C7))

 

[DaRTH KiRo]

Thank you, I did update my profile and this is working now! However, I am now trying to SUMPRODUCT if both column B and C on the Data sheet are equal and I'm running into an issue of it counting the blanks. I've tried using NOT(ISBLANK()) and that didn't seem to work, do you have any thoughts on what I could add to fix that?

 

[DaRTH KiRo]

Please update your account details to indicate which version of Excel you are running.
Different solutions to problems often depend on the version you're running.

One way:

Book1
	A	B
1	Employee	Total
2	Name1	3
3	Name2	0
4	Name3	1
Totals

Cell Formulas
Range	Formula
B2:B4	B2	=SUMPRODUCT((Data!A2:A7=A2)*(Data!B2:B7>Data!C2:C7))

Thank you, I did update my profile and this is working now! However, I am now trying to SUMPRODUCT if both column B and C on the Data sheet are equal and I'm running into an issue of it counting the blanks. I've tried using NOT(ISBLANK()) and that didn't seem to work, do you have any thoughts on what I could add to fix that?

 

[kweaver]

Please show a few examples of all of the conditions you want and the expected results. Please use XL2BB to illustrate.

Do you mean this?

Book1
	A	B	C
1	Employee	Total W>L	Total W=L
2	Name1	3	0
3	Name2	0	0
4	Name3	1	1
Totals

Cell Formulas
Range	Formula
B2:B4	B2	=SUMPRODUCT((Data!$A2:$A10=$A2)*(Data!$B2:$B10>Data!$C2:$C10))
C2:C4	C2	=SUMPRODUCT((Data!$A2:$A10=$A2)*(Data!$B2:$B10=Data!$C2:$C10))