# sumproduct.....or countif

#### bluepenink

##### Well-known Member
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!

### Excel Facts

Lock one reference in a formula
Need 1 part of a formula to always point to the same range? use \$ signs: \$V\$2:\$Z\$99 will always point to V2:Z99, even after copying

#### barry houdini

##### MrExcel MVP
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

##### Legend
Check for leading or trailing spaces in the data.

#### bluepenink

##### Well-known Member
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

##### Legend
You need to include a <>"" statement

#### bluepenink

##### Well-known Member
so something like this?

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

Last edited:

#### bluepenink

##### Well-known Member
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>
</TD></TR><TR><TD bgColor=#ffffff height=6>
1. Suspect​
</TD></TR><TR><TD bgColor=#ffffff height=6>
</TD></TR></TBODY></TABLE>

#### Haseeb Avarakkan

##### Well-known Member
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<>""))

Replies
17
Views
613
Replies
2
Views
229
Replies
27
Views
568
Replies
0
Views
93
Replies
3
Views
191

1,191,192
Messages
5,985,218
Members
439,948
Latest member
Xearo96

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back