Excel Formulas with multiple criteria

S

Shazaxacpcc

Can anyone help me write a formula which would produce the following result.
I have three colums, two have criteria and the third has values in the cells
that need to be added if the criteria in the other two columns matches. The
worksheet is very large containing over 42,000 date rows.

Any assistance greatly appreciated
 
M

Mike H

Hi,

You don't say what the values are, text or numbers so:-

This for text
=SUMPRODUCT((A1:A42000="A")*(B1:B42000="B")*(C1:C42000))

This for numbers
=SUMPRODUCT((A1:A42000=1)*(B1:B42000=2)*(C1:C42000))

Mike
 
P

PCLIVE

Try SUMPRODUCT:

=SUMPRODUCT(--(A2:A100="Criteria1"),--(B2:B100="Criteria2),C2:C100)

HTH,
Paul
 
S

Shazaxacpcc

Thanks for the advice, The first criteria column is text, the second is
number and the column that I need to add up if the criteria is met is a
currency value. Would this change the formula?
 
M

Mike H

It then becomes a mix of the 2


=SUMPRODUCT((A1:A42000="MyText")*(B1:B42000=9999)*(C1:C42000))

Mike
 
S

SUMSUE

Thanks Paul
I've keyed in the following formula and it's returning a nil value, am I
missing something?
=SUMPRODUCT(--(A2:A42655="FBS"),--(K2:K24655="<31"),F2:F42655)

Thanks again
Shaz
 
P

PCLIVE

Second criteria. Change the operator and remove the quotes.

=SUMPRODUCT(--(A2:A42655="FBS"),--(K2:K24655<31),F2:F42655)

Does that help?


--
 
S

Shazaxacpcc

Paul, you are a star!!. How would the formula change of I wanted to do more
than 30 but less than 61??
 
P

PCLIVE

Try this:

=SUMPRODUCT(--(A2:A42655="FBS"),--(K2:K24655>31),--(K2:K24655<61),F2:F42655)

Regards,
Paul

--
 
Top