sum product issue

[bluepenink]

Hello all

Just one quick inquiry as I'm struggling with a simple issue.

I have three sum products on my workbook

1) searches for sales ppl whose revenue change (by country, column i,) was > +10%
=SUMPRODUCT(--('Contest Effectiveness 2011'!$I$10:$I$137='Summary 2011'!J$16)*--('Contest Effectiveness 2011'!$P$10:$P$137>K12))

where K12 = +10%
column P = % breakdown

2) searches for sales ppl whose revenue change (by country, column i,) was < -10%
=SUMPRODUCT(--('Contest Effectiveness 2011'!$I$10:$I$137='Summary 2011'!J$16)*--('Contest Effectiveness 2011'!$P$10:$P$137>M12))

where M12 = < -10%
where P = % breakdown


The final and third that im struggling to define is the in-between (+0 and +9.99%)???

can someple help me define this one, pls and much appreciated!

 

[edvwvw]

Try this

=(SUMPRODUCT(--('Contest Effectiveness 2011'!$I$10:$I$137='Summary 2011'!J$16)*('Contest Effectiveness 2011'!$P$10:$P$137>0)))-(SUMPRODUCT(--('Contest Effectiveness 2011'!$I$10:$I$137='Summary 2011'!J$16)*('Contest Effectiveness 2011'!$P$10:$P$137<.10)))

edvwvw

 

[bluepenink]

Hello thxs alot.

i had to change the lookup table reference, but it appears to be working:

=(SUMPRODUCT(--('Contest Effectiveness 2011'!$H$10:$H$137='Summary 2011'!$J28)*('Contest Effectiveness 2011'!$P$10:$P$137>=0)))-(SUMPRODUCT(--('Contest Effectiveness 2011'!$H$10:$H$137='Summary 2011'!$J28)*('Contest Effectiveness 2011'!$P$10:$P$137<=0.1)))

if i say <=.10, that will also take values that equal 10% correct?

 

[edvwvw]

Yes that will take in 10% as well

The expression that you are using translates to "less than or equal to"

edvwvw

 

[bluepenink]

Hello

again, thxs for your quick response. im a little baffled as my #s are not matching up.

here are my formulas:

1)
>=10%....................COUNT with formula is 70 ppl; manual count shows 68 ppl (DIFF. OF 2 PPL)???!?!?

=SUMPRODUCT(--('Contest Effectiveness 2011'!$H$10:$H$137='Summary 2011'!$J29)*--('Contest Effectiveness 2011'!$P$10:$P$137>=K$25))

k25 = 10%

2)

<= -10%...........count with formula is 43; maual is 43 (THIS IS OK)
=SUMPRODUCT(--('Contest Effectiveness 2011'!$H$10:$H$137='Summary 2011'!$J29)*--('Contest Effectiveness 2011'!$P$10:$P$137<=L$25))

L25 = -10%

3)
> 0 & <9.99%.............COUNT with formula is 23 ppl; manual count shows 7 ppl between 0-9.99%......(BIG VARIANCE)!!???

=(SUMPRODUCT(--('Contest Effectiveness 2011'!$H$10:$H$137='Summary 2011'!$J29)*('Contest Effectiveness 2011'!$P$10:$P$137>$M$24)))-(SUMPRODUCT(--('Contest Effectiveness 2011'!$H$10:$H$137='Summary 2011'!$J29)*('Contest Effectiveness 2011'!$P$10:$P$137<=$M$25)))


i have total of 128 ppl; if i manually add them,

68 ppl > 10%
43 < - 10%
7 ppl between 0-9.99%
that gives total of 118, with 10 ppl that have "-" or are in between -1% to -9.99%

any reason why? thxs alot!

 

[edvwvw]

without seeing your data I do not know why it is giving conflicting answers. Sumproduct is very particular about text and numerical formatting.

It could be to do with formatting of values. I notice that column P might have a "-", that may lead to errors - can you leave the cell blank. If you get a #value error use right click clear contents.

I need to sign off now - will not be back until Tuesday.

edvwvw