How do I use COUNTIF if I am combining 2 columns?

G

Guest

I am trying to make a count of sales by salesperson. In column I, I have
"salesperson(DP,PR,BD)", and in column N, I have "sales status (S,NI,L,P,F)".
I want to find out how many "sales(S)", each person has. In a description
=COUNTIF(N:N,"=DP")IF(I:I,"=S") this is basically what I am trying to say. I
know that this is not the correct formula, I have been trying others, however
I haven't had success.
 
D

Domenic

Try...

=SUMPRODUCT(--(N1:N100="DP"),--(I1:I100="S"))

Note that SUMPRODUCT does not accept whole column references.

Hope this helps!
 
G

Guest

Duke,
Thank you for your help. I did not know that in that formula you could
not use the whole column. You just saved my weekend.
 
G

Guest

Domenic,
Thank you so much for your help. I did not know that SUMPRODUCT couldn't
use the whole column. You saved my weekend and my afternoon. Again Thanks
 
G

Guest

Hi,

You may use an array formula (Ctrl+Shift+Enter) to arrive at the same result

=count(if((range1="--")*(range2="--"),1,0))

Regards,

Ashish Mathur
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

how do I sum using 3 reference columns? 3
How do I sum with 3 reference columns? 2
how do I sumif using 3 reference columns? 3
Countif help needed 6
How do I count letters and numbers in a formula? 4
COUNTIF over 2 columns 0
Combination of IF, AND, and COUNTIF; need help 6
Can I use COUNTIF for 2 separate IF queries? 5

Top