I've tried the SUMIF function but i really need to get one figure with two
different conditions each coming from a different column and i can't quite
find anything to do the job. Can you please help me?

Hi!

Try something like this:

Condition 1 = Yes
Condition 2 = 100
C1:C100 = range to sum

=SUMPRODUCT(--(A1:A100="Yes"),--(B1:B100=100),C1:C100)

Notice that the TEXT condition is enclosed in quotes and the NUMERIC
condition is not.

Biff

"Melanie" wrote in message
...
I've tried the SUMIF function but i really need to get one figure with two
different conditions each coming from a different column and i can't quite
find anything to do the job. Can you please help me?

I tried your formula for the project i am working on it didnt quite give me
the answer i needed.

The formula i need is more like IF G3:G215 = "Salespersons Name" and If
J3:215 = "Quote Status" then find the corrosponding figure from I3:I15.

This is why i orginally tried SUMIF only to find i couldn't get it to do two
ranges each with a corrosponding condition to find a combined total from.

I'm not sure if what i am asking makes any sense.

Also when i looked at SUMPRODUCT function in the help menu i found this
multiplies the value im after the total with both conditions applied with no
modifications.

"Biff" wrote:

Hi!

Try something like this:

Condition 1 = Yes
Condition 2 = 100
C1:C100 = range to sum

=SUMPRODUCT(--(A1:A100="Yes"),--(B1:B100=100),C1:C100)

Notice that the TEXT condition is enclosed in quotes and the NUMERIC
condition is not.

Biff

"Melanie" wrote in message
...
I've tried the SUMIF function but i really need to get one figure with two
different conditions each coming from a different column and i can't quite
find anything to do the job. Can you please help me?

Biff's formula work, don't worry about the help, MS had no clues it could
work this way when the help was written, you just need to adapt it

=SUMPRODUCT(--(G3:G215 = "Salespersons Name"),--( J3:215 = "Quote Status),
I3:I215)

will retrun any number from I3:I215 (I assume you had a type and not I15
because it needs ti be the same size
as the other 2)

If the values in I3:I215 are text

=INDEX( I3:I215,MATCH(1,(G3:G215 = "Salespersons Name")*(J3:215 = "Quote
Status),0))

entered with ctrl + shift & enter

it would be better to replace the criteria like "Salespersons Name" with for
instance A3 or any other cell and put the criteria in that cell, do the same
for quiote status, that way you don't have to edit the formula when you
change sales person name

--


Regards,

Peo Sjoblom

Excel 95 - Excel 2007
Northwest Excel Solutions
www.nwexcelsolutions.com
(remove ^^ from email)


"Melanie" wrote in message
...
I tried your formula for the project i am working on it didnt quite give me
the answer i needed.

The formula i need is more like IF G3:G215 = "Salespersons Name" and If
J3:215 = "Quote Status" then find the corrosponding figure from I3:I15.

This is why i orginally tried SUMIF only to find i couldn't get it to do
two
ranges each with a corrosponding condition to find a combined total from.

I'm not sure if what i am asking makes any sense.

Also when i looked at SUMPRODUCT function in the help menu i found this
multiplies the value im after the total with both conditions applied with
no
modifications.

"Biff" wrote:

Hi!

Try something like this:

Condition 1 = Yes
Condition 2 = 100
C1:C100 = range to sum

=SUMPRODUCT(--(A1:A100="Yes"),--(B1:B100=100),C1:C100)

Notice that the TEXT condition is enclosed in quotes and the NUMERIC
condition is not.

Biff

"Melanie" wrote in message
...
I've tried the SUMIF function but i really need to get one figure with
two
different conditions each coming from a different column and i can't
quite
find anything to do the job. Can you please help me?

I3:I15.

Is that a typo? Did you mean I3:I215?

I'll assume you did.

So, you want to sum values in I3:I215 that correspond to G3:G215 =
"Salespersons Name" and J3:215 = "Quote Status" ?

=SUMPRODUCT(--(G3:G215="Salespersons Name"),--(J3:215="Quote
Status"),I3:I215)

Also when i looked at SUMPRODUCT function in the help menu i found this
multiplies the value

Yes, that's what it does THEN it sums the results of that multiplication.

For example:

G3 = "Salespersons Name" * J3 = "Quote Status" * I3

If G3 is TRUE and J3 is TRUE then this is what you get:

1*1*I3 = I3

If either G3 or J3 is FALSE then this is what you get:

0*1*I3 = 0
1*0*I3 = 0

Biff

"Melanie" wrote in message
...
I tried your formula for the project i am working on it didnt quite give me
the answer i needed.

The formula i need is more like IF G3:G215 = "Salespersons Name" and If
J3:215 = "Quote Status" then find the corrosponding figure from I3:I15.

This is why i orginally tried SUMIF only to find i couldn't get it to do
two
ranges each with a corrosponding condition to find a combined total from.

I'm not sure if what i am asking makes any sense.

Also when i looked at SUMPRODUCT function in the help menu i found this
multiplies the value im after the total with both conditions applied with
no
modifications.

"Biff" wrote:

Hi!

Try something like this:

Condition 1 = Yes
Condition 2 = 100
C1:C100 = range to sum

=SUMPRODUCT(--(A1:A100="Yes"),--(B1:B100=100),C1:C100)

Notice that the TEXT condition is enclosed in quotes and the NUMERIC
condition is not.

Biff

"Melanie" wrote in message
...
I've tried the SUMIF function but i really need to get one figure with
two
different conditions each coming from a different column and i can't
quite
find anything to do the job. Can you please help me?

Yes that was a typo i was meaning I215

Yes see i what i have is four salespersons and five quote status' therefore
i want to (each in a individual cell) obtain a total figure for each status
that i have for each sales person.

In this effect there will be a total for

Sales person1 for Pending quotes
Sales person1 for dead quotes
etc

Sales person2 for pending quotes
Sales person2 for dead quotes
etc

each in invidual cells hence needing individual formula's

for each formula/function needed in the cell it needs to obtain a figure
from 'Worksheet1'!$I$3:$I$215 BUT in doing so i needs to check $G$3:$G$215 to
ensure it is equal to salesperson1 (or2 depending on which formula i am
creating) and to check $J$3:$J$215 to ensure it is pending quote (or dead
quotes, once again depending on which formula i am creating at the time).

It's quite a complicated procedure i am doing (at least to me) so im not
sure if i am conveying exactly what i want in a clear manner.

This information i am retriving from worksheet 1 to say worksheet 2 i am
ultimately creating to use as data for a chart later in the process.

"Biff" wrote:

I3:I15.

Is that a typo? Did you mean I3:I215?

I'll assume you did.

So, you want to sum values in I3:I215 that correspond to G3:G215 =
"Salespersons Name" and J3:215 = "Quote Status" ?

=SUMPRODUCT(--(G3:G215="Salespersons Name"),--(J3:215="Quote
Status"),I3:I215)

Also when i looked at SUMPRODUCT function in the help menu i found this
multiplies the value

Yes, that's what it does THEN it sums the results of that multiplication.

For example:

G3 = "Salespersons Name" * J3 = "Quote Status" * I3

If G3 is TRUE and J3 is TRUE then this is what you get:

1*1*I3 = I3

If either G3 or J3 is FALSE then this is what you get:

0*1*I3 = 0
1*0*I3 = 0

Biff

"Melanie" wrote in message
...
I tried your formula for the project i am working on it didnt quite give me
the answer i needed.

The formula i need is more like IF G3:G215 = "Salespersons Name" and If
J3:215 = "Quote Status" then find the corrosponding figure from I3:I15.

This is why i orginally tried SUMIF only to find i couldn't get it to do
two
ranges each with a corrosponding condition to find a combined total from.

I'm not sure if what i am asking makes any sense.

Also when i looked at SUMPRODUCT function in the help menu i found this
multiplies the value im after the total with both conditions applied with
no
modifications.

"Biff" wrote:

Hi!

Try something like this:

Condition 1 = Yes
Condition 2 = 100
C1:C100 = range to sum

=SUMPRODUCT(--(A1:A100="Yes"),--(B1:B100=100),C1:C100)

Notice that the TEXT condition is enclosed in quotes and the NUMERIC
condition is not.

Biff

"Melanie" wrote in message
...
I've tried the SUMIF function but i really need to get one figure with
two
different conditions each coming from a different column and i can't
quite
find anything to do the job. Can you please help me?

See this screencap:

http://img92.imageshack.us/img92/4171/sampleel7.jpg

Enter the formula in B2 and copy across to F2 then down to row 5.

Biff

"Melanie" wrote in message
...
Yes that was a typo i was meaning I215

Yes see i what i have is four salespersons and five quote status'
therefore
i want to (each in a individual cell) obtain a total figure for each
status
that i have for each sales person.

In this effect there will be a total for

Sales person1 for Pending quotes
Sales person1 for dead quotes
etc

Sales person2 for pending quotes
Sales person2 for dead quotes
etc

each in invidual cells hence needing individual formula's

for each formula/function needed in the cell it needs to obtain a figure
from 'Worksheet1'!$I$3:$I$215 BUT in doing so i needs to check $G$3:$G$215
to
ensure it is equal to salesperson1 (or2 depending on which formula i am
creating) and to check $J$3:$J$215 to ensure it is pending quote (or dead
quotes, once again depending on which formula i am creating at the time).

It's quite a complicated procedure i am doing (at least to me) so im not
sure if i am conveying exactly what i want in a clear manner.

This information i am retriving from worksheet 1 to say worksheet 2 i am
ultimately creating to use as data for a chart later in the process.

"Biff" wrote:

I3:I15.

Is that a typo? Did you mean I3:I215?

I'll assume you did.

So, you want to sum values in I3:I215 that correspond to G3:G215 =
"Salespersons Name" and J3:215 = "Quote Status" ?

=SUMPRODUCT(--(G3:G215="Salespersons Name"),--(J3:215="Quote
Status"),I3:I215)

Also when i looked at SUMPRODUCT function in the help menu i found this
multiplies the value

Yes, that's what it does THEN it sums the results of that multiplication.

For example:

G3 = "Salespersons Name" * J3 = "Quote Status" * I3

If G3 is TRUE and J3 is TRUE then this is what you get:

1*1*I3 = I3

If either G3 or J3 is FALSE then this is what you get:

0*1*I3 = 0
1*0*I3 = 0

Biff

"Melanie" wrote in message
...
I tried your formula for the project i am working on it didnt quite give
me
the answer i needed.

The formula i need is more like IF G3:G215 = "Salespersons Name" and If
J3:215 = "Quote Status" then find the corrosponding figure from I3:I15.

This is why i orginally tried SUMIF only to find i couldn't get it to
do
two
ranges each with a corrosponding condition to find a combined total
from.

I'm not sure if what i am asking makes any sense.

Also when i looked at SUMPRODUCT function in the help menu i found this
multiplies the value im after the total with both conditions applied
with
no
modifications.

"Biff" wrote:

Hi!

Try something like this:

Condition 1 = Yes
Condition 2 = 100
C1:C100 = range to sum

=SUMPRODUCT(--(A1:A100="Yes"),--(B1:B100=100),C1:C100)

Notice that the TEXT condition is enclosed in quotes and the NUMERIC
condition is not.

Biff

"Melanie" wrote in message
...
I've tried the SUMIF function but i really need to get one figure
with
two
different conditions each coming from a different column and i can't
quite
find anything to do the job. Can you please help me?