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

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.

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

"tw5633" wrote:

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.

Try...

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

Note that SUMPRODUCT does not accept whole column references.

Hope this helps!

In article ,
tw5633 wrote:

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.

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.


"Duke Carey" wrote:

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

"tw5633" wrote:

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.

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

"Domenic" wrote:

Try...

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

Note that SUMPRODUCT does not accept whole column references.

Hope this helps!

In article ,
tw5633 wrote:

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.

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

"tw5633" wrote:

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.