Formula for each employees sales over 2 help

[Ramenmole]

I would like a formula to add up sale 1 and sale 2 columns for each employee and tell me how many times they both add up to more than 2 with the results on a second sheet named sum for the attached spreadsheet

Thanks in advance if this can be done

[azumi]

Put in B2 on sheet SUM and copied down

=SUMPRODUCT((Sales!$A$3:$A$28=A2)*(Sales!$B$3:$C$28))

[Ramenmole]

Thanks Azumi

i don't think i explained that very well

I want to know how many times each employee has gone over 2 sales (of sale 1 and sale 2 columns added together)
The first name is Alan and he had 2 (sale 1) and 1 (sale 2) total =3 and on row 16 Alan had Nil (sale 1) and 3 (sale 2) total =3
So on Sheet "sum" in b2 next to his name should be 2 as both his sales totals have been over 2

hope that explains it better

[Richard Buttrey]

Hi,

Maybe B2 copied down

=COUNTIFS(Sales!$A$3:$A$29,A2,Sales!B3:B29,">"&1)+COUNTIFS(Sales!$A$3:$A$29,A2,Sales!$C$3:$C$29,">"&1)

[Ramenmole]

Thanks Richard

but its showing 2 members with 3, which can't be correct as they are only on this list twice so the maximum number can only be 2 (over 2 total sales)

[JohnTopley]

Try

=SUMPRODUCT((--((Sales!$A$3:$A$28=Sum!$A2)*(Sales!$B$3:$B$28)+(Sales!$A$3:$A$28=Sum!$A2)*(Sales!$C$3:$C$28)>=3)))

[Ramenmole]

Thank you John that has done the trick