sumproduct

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

With regards to your second request:

Your SUMPRODUCT() isn't specifying >=75%

I would say you have 2 matching entries that are 75% so that the formula as is is totalling them and giving you 1.5.

Regarding your first request:

Are you certain you filtered for same criteria as the formula?

hello

i thought i was specific when fsaying in my formula >=H7 aka 75%?

the column "S" in the north america summary page is down down selection of entires
i.e. 25%, 50%, 75%, 100%

so i wrote out, >=H7, where H7 is 75%.

...and also, yes, i did manually filter for wash-phil region and for some reason it was showing up 75% entries as 8 times, instead of 6 that i found when i did it manually :S...any suggestions or thoughts? thx u

Originally Posted by Cutter

With regards to your second request:

Your SUMPRODUCT() isn't specifying >=75%

I would say you have 2 matching entries that are 75% so that the formula as is is totalling them and giving you 1.5.

hello, just wondering wht mean by the following
"I would say you have 2 matching entries that are 75% so that the formula as is is totalling them and giving you 1.5"

also, how can i specify >=75% in this formula?
=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))

basically, column S is where 75% etc are in for, anyone have any suggestions?

The first formula in your original post is checking for >=75% but your second formula is not.

You left out the >=75% (or =H7). Take a step back and carefully go over that second formula, comparing the last part of the first one to the last part of the second one.

The second formula, as you have it, is SUMMING the values found in col S where your criteria is met in col D and col R.

Your first formula is COUNTING the matches in col S where criteria for col D is met.

how do i go about doing that...thas my issue...let me know, pls and thx u

this is an array formula as follows:

=SUM(if('North America Summary'!$D$9:$D$1852=$C15,1,0)*if(('North America Summary'!$S$9:$S$1852>=$H$7,1,0)*ARRAY YOU WANT TO SUM) use control shift enter so that you see { } around your formula

Originally Posted by Cutter

Your first formula copied from your original post is:

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

Note that it has one too many closing brackets at the end.

Your second formula copied from your original post is:

=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 not see that you are missing the >=$H$7 in the second formula????
And the leading double unary and opening bracket in that last component.

So your second formula should be changed to:

=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>=$H$7))

Constructed by simply placing your extra component - namely: --('North America Summary'!$R$9:$R$1852=$F$7)
between the 2 components of your first formula, adding a comma and removing that extra bracket at the end.

hello cutter,

thxs for your time and explaining the formula, its working like a charm for sure.

in terms of the extra brackets, i realize i had those (i had an if statement before the sumprod) portion so i simlpy just took out the sumprod formula portion and pasted it into the this thread (my apologies).

but your sumprod is working great. it look at total # and then checks how many are in sales phase (4) and how many are then are =>75%. - thx yuo so much!

one final question,

i have a stacked bar which shows stacked together
# of deals, sales phase (4) and greater than 75%.

now, my stacked bar shows for wash-phil
# of deals - 11
sales phase four - 8
greater than 75% close rate - 5

i was told if i simply show those, that will show a total of (11+8+5)=24 on the y-axis and ppl will interpret it in the wrong way, thinking there are a total of 24 deals, when really there is a total of 11 deals.

so should i just simply do
# of deals = 8 (11 - (8-3))
sales phase four = 8
greater than 75% close rate - 5

sorry but its just confusing me, thx u so much for your great help!!!

I would suggest you mark this thread as SOLVED and start a new thread regarding your chart.

Hopefully your new thread will catch Andy Pope's eye. He is the graph master from what I've seen.

Originally Posted by John Vieren

this is an array formula as follows:

=SUM(if('North America Summary'!$D$9:$D$1852=$C15,1,0)*if(('North America Summary'!$S$9:$S$1852>=$H$7,1,0)*ARRAY YOU WANT TO SUM) use control shift enter so that you see { } around your formula

hello john

i tried to use ur formula, but excel keeps giving me an error
=SUM(if('North America Summary'!$D$9:$D$1852=$C15,1,0)*if(('North America Summary'!$S$9:$S$1852>=$H$7,1,0)*('North America Summary'!$S$9:$S$1852))