Need function for one value for two criterias and two columns

M

Melanie

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

Biff

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
 
M

Melanie

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

Peo Sjoblom

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

Biff

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
Click to expand...

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
 
M

Melanie

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