not sure if it would be sumif or if

K

katrina

Hi There - hoping someone can help. I need to be able to count and sum
a set of data based on a persons name and an out come code. I have
tried using the following formula

=IF('[Leads Results Template.xls]Status'!$P$1:$P$1000=Q4,SUMIF('[Leads
Results Template.xls]Status'!$I$1:$I$1000,Q17,'[Leads Results
Template.xls]Status'!$M$1:$M$1000),0)

In translation if the persons name (Q4) is contained in the lookup
range($P$1:$P$1000) I want it to then evaluate a second column for
phrase "New Contract" , "New Existing" and "new Order" (if this is
present I would like it to add any values in the Value Column only if
it equals the persons name tho'.

Any suggestions would be appreciated.

Cheers
Katrina
 
B

Bob Phillips

=SUMPRODUCT(--('[Leads Results Template.xls]Status'!$P$1:$P$1000=Q4),
--('[Leads Results Template.xls]Status'!$I$1:$I$1000=Q17),
'[Leads Results Template.xls]Status'!$M$1:$M$1000)


--
---
HTH

Bob

(change the xxxx to gmail if mailing direct)
 
D

daddylonglegs

What's in Q17? If that's "New Contract" then perhaps

=SUMPRODUCT(--('[Leads Results
Template.xls]Status'!$P$1:$P$1000=Q4),--('[Leads
Results Template.xls]Status'!$I$1:$I$1000=Q17),'[Leads Results
Template.xls]Status'!$M$1:$M$1000)

If you want to check for any of the terms you listed and these are in
Q17:Q19 for instance then amend to

=SUMPRODUCT(--('[Leads Results
Template.xls]Status'!$P$1:$P$1000=Q4),--ISNUMBER(MATCH('[Leads
Results Template.xls]Status'!$I$1:$I$1000,Q17:Q19,0)),'[Leads Results
Template.xls]Status'!$M$1:$M$1000)
 
K

katrina

Thanks for that - although neither seem to work I get a #n/A response

I have tried breaking the formula down using the wizard but nothing
seems to work ... should i have some sort of analysis tool installed?

i have also tried using the following formula (have been looking on
other sites for answers as well but it too returns an #n/a value

=SUMPRODUCT('[Leads Results
Template.xls]Status'!$P$1:$P$853=Q3)*('[Leads Results
Template.xls]Status'!$P$1:$P$853=P3)*('[Leads Results
Template.xls]Status'!$M$1:$M$853)

im stumped im sorry ...
What's in Q17? If that's "New Contract" then perhaps

=SUMPRODUCT(--('[Leads Results
Template.xls]Status'!$P$1:$P$1000=Q4),--('[Leads
Results Template.xls]Status'!$I$1:$I$1000=Q17),'[Leads Results
Template.xls]Status'!$M$1:$M$1000)

If you want to check for any of the terms you listed and these are in
Q17:Q19 for instance then amend to

=SUMPRODUCT(--('[Leads Results
Template.xls]Status'!$P$1:$P$1000=Q4),--ISNUMBER(MATCH('[Leads
Results Template.xls]Status'!$I$1:$I$1000,Q17:Q19,0)),'[Leads Results
Template.xls]Status'!$M$1:$M$1000)

"> >
Click to expand...
 
D

dodong

Bob said:
=SUMPRODUCT(--('[Leads Results Template.xls]Status'!$P$1:$P$1000=Q4),
--('[Leads Results Template.xls]Status'!$I$1:$I$1000=Q17),
'[Leads Results Template.xls]Status'!$M$1:$M$1000)


--
---
HTH

Bob

(change the xxxx to gmail if mailing direct)


katrina said:
Hi There - hoping someone can help. I need to be able to count and sum
a set of data based on a persons name and an out come code. I have
tried using the following formula

=IF('[Leads Results Template.xls]Status'!$P$1:$P$1000=Q4,SUMIF('[Leads
Results Template.xls]Status'!$I$1:$I$1000,Q17,'[Leads Results
Template.xls]Status'!$M$1:$M$1000),0)

In translation if the persons name (Q4) is contained in the lookup
range($P$1:$P$1000) I want it to then evaluate a second column for
phrase "New Contract" , "New Existing" and "new Order" (if this is
present I would like it to add any values in the Value Column only if
it equals the persons name tho'.

Any suggestions would be appreciated.

Cheers
Katrina
Click to expand...
Click to expand...


The formula given by Bob is correct except that there is a missing open
parenthesis in the last part.

=SUMPRODUCT(--('[Leads Results
Template.xls]Status'!$P$1:$P$1000=Q4),--('[Leads Results
Template.xls]Status'!$I$1:$I$1000=Q17),('[Leads Results
Template.xls]Status'!$M$1:$M$1000))
 
B

Bob Phillips

No there isn't. It is not necessary to enclose the final array in
parentheses as there is no condition involved.

--
---
HTH

Bob

(change the xxxx to gmail if mailing direct)


dodong said:
Bob said:
=SUMPRODUCT(--('[Leads Results Template.xls]Status'!$P$1:$P$1000=Q4),
--('[Leads Results Template.xls]Status'!$I$1:$I$1000=Q17),
'[Leads Results Template.xls]Status'!$M$1:$M$1000)


--
---
HTH

Bob

(change the xxxx to gmail if mailing direct)


katrina said:
Hi There - hoping someone can help. I need to be able to count and sum
a set of data based on a persons name and an out come code. I have
tried using the following formula

=IF('[Leads Results Template.xls]Status'!$P$1:$P$1000=Q4,SUMIF('[Leads
Results Template.xls]Status'!$I$1:$I$1000,Q17,'[Leads Results
Template.xls]Status'!$M$1:$M$1000),0)

In translation if the persons name (Q4) is contained in the lookup
range($P$1:$P$1000) I want it to then evaluate a second column for
phrase "New Contract" , "New Existing" and "new Order" (if this is
present I would like it to add any values in the Value Column only if
it equals the persons name tho'.

Any suggestions would be appreciated.

Cheers
Katrina
Click to expand...
Click to expand...


The formula given by Bob is correct except that there is a missing open
parenthesis in the last part.

=SUMPRODUCT(--('[Leads Results
Template.xls]Status'!$P$1:$P$1000=Q4),--('[Leads Results
Template.xls]Status'!$I$1:$I$1000=Q17),('[Leads Results
Template.xls]Status'!$M$1:$M$1000))
Click to expand...
 
B

Bob Phillips

Katrina,

Does the other workbook have #N/A in any of the cells?

--
---
HTH

Bob

(change the xxxx to gmail if mailing direct)


katrina said:
Thanks for that - although neither seem to work I get a #n/A response

I have tried breaking the formula down using the wizard but nothing
seems to work ... should i have some sort of analysis tool installed?

i have also tried using the following formula (have been looking on
other sites for answers as well but it too returns an #n/a value

=SUMPRODUCT('[Leads Results
Template.xls]Status'!$P$1:$P$853=Q3)*('[Leads Results
Template.xls]Status'!$P$1:$P$853=P3)*('[Leads Results
Template.xls]Status'!$M$1:$M$853)

im stumped im sorry ...
What's in Q17? If that's "New Contract" then perhaps

=SUMPRODUCT(--('[Leads Results
Template.xls]Status'!$P$1:$P$1000=Q4),--('[Leads
Results Template.xls]Status'!$I$1:$I$1000=Q17),'[Leads Results
Template.xls]Status'!$M$1:$M$1000)

If you want to check for any of the terms you listed and these are in
Q17:Q19 for instance then amend to

=SUMPRODUCT(--('[Leads Results
Template.xls]Status'!$P$1:$P$1000=Q4),--ISNUMBER(MATCH('[Leads
Results Template.xls]Status'!$I$1:$I$1000,Q17:Q19,0)),'[Leads Results
Template.xls]Status'!$M$1:$M$1000)

"> >
Click to expand...
Click to expand...
 
K

katrina

HI Bob,

Thanks so much for your assistance ... it did have 3 #n/a's when
removed formula worked fine ... now my next task is to count the
number of outomes of contract, new order and reorder per persons name.
Im assuming i can use a similar sort of formula.

thanks again
Katrina
 
B

Bob Phillips

=SUMPRODUCT(--('[Leads Results Template.xls]Status'!$P$1:$P$1000=Q4),
--('[Leads Results Template.xls]Status'!$I$1:$I$1000=Q17))


--
---
HTH

Bob

(change the xxxx to gmail if mailing direct)
 
Top