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

T

tw5633

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!
 
T

tw5633

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.
 
T

tw5633

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
 
A

Ashish Mathur

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
Combination of IF, AND, and COUNTIF; need help 6
How do I count letters and numbers in a formula? 4
Counting unique values based on 2 columns 3
Can I use COUNTIF for 2 separate IF queries? 5
How do I get an average sale if multiple columns? 9

Top