sumproduct.....or countif

[bluepenink]

Hello

just looking to make sure my logic is correct bc it seems a lil off.

=SUMPRODUCT(--('North America Summary'!$D$9:$D$1852=$C11),--('North America Summary'!$S$9:$S$1852>=$H$7),'North America Summary'!$R$9:$R$1852))

basically, im trying to count (using excel 03)...to see, if a particular city i.e. boston, have how many deals in the sales phase 4 that is greater than 75%.

D9:D1852 = city names; c10 = city name (i.e boston)
S9:S1852 = probality of close; h7 = 75%
r9:r1852 = sales phase 4

so basically, check the city name, if it matches, then count how many are in the probability of close => 75% and give me the # that is in sales phase 4.

can someone pls help, bc thats giving me 0. thx you!

 

[barry houdini]

The syntax looks right - are there numbers in column R?

Check that the other criteria work alone - do you get non-zero results using either

=COUNTIF('North America Summary'!$D$9:$D$1852,$C11)

or

=COUNTIF('North America Summary'!$S$9:$S$1852,">="&$H$7)

 

[VoG]

Check for leading or trailing spaces in the data.

 

[bluepenink]

there are quite a few blanks in the column or empty spaces.

this is what i had originally, just counting the # of cities with possibility of 75% deal closing rate:

=SUMPRODUCT(--('North America Summary'!$D$9:$D$1852=$C10),--('North America Summary'!$S$9:$S$1852>=$H$7)))
..which gives me for i.e. Atlanta-Charleston a total of ...5
...total # of deals atl-char has is 160
...deals in sales phase four is 3

but i cant just simply do a subtraction of deals - sales phase or something bc there are some empty fields in the 160 deals for that region etc.

any ideas guys? much appreciated.

 

[VoG]

You need to include a <>"" statement

 

[bluepenink]

so something like this?

=SUMPRODUCT(--('North America Summary'!$D$9:$D$1852=$C10),--('North America Summary'!$S$9:$S$1852<>"")))

 

[bluepenink]

The syntax looks right - are there numbers in column R?

Check that the other criteria work alone - do you get non-zero results using either

=COUNTIF('North America Summary'!$D$9:$D$1852,$C11)

or

=COUNTIF('North America Summary'!$S$9:$S$1852,">="&$H$7)

no, there not #s but rather
<TABLE dir=ltr borderColor=#000000 cellSpacing=1 cellPadding=2 width=44 border=1><TBODY><TR><TD bgColor=#ffffff height=6>

2. Contact Made​

​

​

​

​

</TD></TR><TR><TD bgColor=#ffffff height=6>

1. Suspect​

​

​

​

​

</TD></TR><TR><TD bgColor=#ffffff height=6>

2. Contact Made​

​

​

​

​

</TD></TR></TBODY></TABLE>

 

[Haseeb Avarakkan]

Try;

=SUMPRODUCT(--('North America Summary'!$D$9:$D$1852=$C10),--('North America Summary'!$S$9:$S$1852>=$H$7),--('North America Summary'!$R$9:$R$1852<>""))