Countif??

[Gallinski]

Hi all

I'm having issues working out the formula for a countif or dcounta i think i might need.

First column is Salesman and second is company sold to. I'm trying to work out how many the salesman has sold without counting the blank cells. I don't need to know which companies, just the total over all. Obviously this list will increase with more sales.
So if salesman is JA then total companies sold to is ?? etc.

please see attached example.

Thanks.

[Andrew-R]

Try this:

=SUM(--(IFERROR(MATCH($C$6:$C$15,INDEX(IF(($B$6:$B$15="JA")*($C$6:$C$15<>""),$C$6:$C$15,-1),0),0),0)=ROW(INDIRECT("1:" & COUNTA($B$6:$B$15)))))

This is an array formula and must be entered using Ctrl-Shift-Enter, not just enter.

[Gallinski]

No sorry didn't seem to work.

[Gallinski]

It's ok, found a thread which helped with the answer. Used the following formula.

=SUMPRODUCT(--($B$6:$B50="JA"),--($C$6:$C50<>""))

[Andrew-R]

Oh, sorry, I thought you wanted to count the number of *different* companies each person had sold to.