countif with sum function

[bluepenink]

Hi

Just wondering how i can incorporate a countif to lookup or sum values

for example.
I'm looking to count # of deals for a district (i.e. NY-Long Island)

the summary sheet (populated by branches) has NY-Long Island as NY-LI, so my countif is not working.

my formula is as follows:
=COUNTIF('North America Summary'!$G$9:$G$1852,"="&Graph!$C7)

where C7 = district name

now, is it possible for the countif to sum NY-LI, can it be pre-defined? thx you for your time.

 

[stilesbn]

You shouldn't need the "=" in there. Do

=COUNTIF('North America Summary'!$G$9:$G$1852,Graph!$C7)

 

[bluepenink]

Hi

Question

I have the following formula to lookup 2 things in the countif

1) search for city name, match it
and then
2) count # of deals in ..4.Proposed

so i have the following countif
=COUNTIF('North America Summary'!$G$9:$G$1852,$C7)--('North America Summary'!$Q$9:$Q$1852,$F$5)

i tried to use the similar logic to sumproduct, but obv its not functioning.
c7 = city name
f5 = 4. Proposed

G9:G1852 = city name
Q9:Q1852 = sales phase (which includes 4. Proposed).
thx u!

 

[jasonb75]

Assuming you want the formula to remain linked to the cell for instances of anything other than NY-LI, maybe

=COUNTIF('North America Summary'!$G$9:$G$1852,IF(Graph!$C7="NY-Long Island","NY-LI",Graph!$C7))

edit:

=COUNTIFS('North America Summary'!$G$9:$G$1852,$C7,'North America Summary'!$Q$9:$Q$1852,$F$5)

Note that you need excel 07 or newer to use this function, for older versions you need to use sumproduct.

=SUMPRODUCT(--('North America Summary'!$G$9:$G$1852=$C7),--('North America Summary'!$Q$9:$Q$1852=$F$5))

 

[bluepenink]

Hey
thxs for your input. ill stick with the sumproduct as the ppl here use 03 (which suxs) oh well.

im just having some trouble with the sumproduct

=SUMPRODUCT(--('North America Summary'!$D$9:$D$1852=Graph!$C7),--('North America Summary'!$Q$9:$Q$1852=$E$6),'North America Summary'!$Q$9:$Q$1852)

c7 = city name
e6 = forecasted annual revnue

d9:d1852 = city name
q9:q1852 = revenue $

it keeps showing me a 0. (there are instances where the branch ppl have input "potential $100" in one of the cells in column Q...but will that mess up the sumprod?

thxs again!

 

[jasonb75]

Are you trying to sum the revenue in Q where city name matches?

=SUMPRODUCT(--('North America Summary'!$D$9:$D$1852=Graph!$C7),'North America Summary'!$Q$9:$Q$1852)

Your version would only sum those values if the revenue was an exact match for to the forecast value.

 

[bluepenink]

Hey jason

thxs alot for your help and input! ur awesome!

 

[jasonb75]

Your logic appears correct, maybe you missed some entries when you did your visual check, with 1800 + rows it's very easy to misread something.

=SUMPRODUCT(--('North America Summary'!$D$9:$D$1852=Graph!$C8),--('North America Summary'!$R$9:$R$1852=$F$5))

will tell you how many matching rows you should have.

Alternatively, in an spare column, enter the formula below and array confirm with Shift Ctrl Enter

=SMALL(IF('North America Summary'!$D$9:$D$1852=Graph!$C8,IF('North America Summary'!$R$9:$R$1852=$F$5,ROW($A$9:$A$1852))),ROW(A1))

Then autofill down until you get an error result, that will list the rows that the values are being found in.

 

[bluepenink]

Hey Jason

i want to really thx you for your time and input, its much appreciated!...it works like a charm. thx u again! have a great day!

 

[bluepenink]

Hello

i ran into somewhat of an issue.

here is my formula
=SUMPRODUCT(--('North America Summary'!$D$9:$D$1852=$C15),--('North America Summary'!$S$9:$S$1852>=$H$7)))

d9:d1852 = city
c15 = city
s9:s1852 = 75%
h7 = 75%

for i.e washington-philadelphia, its showing a total of 8, which is fine. however, when i do a manual filter check, there should only be 6; any ideas why it might pick two extra entries? i filtered by wash-phil (c15), but for some reason its showing 2 extra entries.

also, the formula is being a lil wierd when it comes to adding another criteria:

basically, i would like to search by city, then sales phase (4) and tell me of those how many fall are 75%

i used this formula, but its giving me 1.5 type entries when it should be whole #s

=SUMPRODUCT(--('North America Summary'!$D$9:$D$1852='Regional - Deals, Sales, Other'!$C10),--('North America Summary'!$R$9:$R$1852=$F$7),'North America Summary'!$S$9:$S$1852))

can you pls help, thxs