Excel Formulas with multiple criteria

[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

 

[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

 

[PCLIVE]

Try SUMPRODUCT:

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

HTH,
Paul

 

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

 

[Mike H]

It then becomes a mix of the 2


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

Mike

 

[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

 

[PCLIVE]

Second criteria. Change the operator and remove the quotes.

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

Does that help?


--

 

[Shazaxacpcc]

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

 

[PCLIVE]

Try this:

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

Regards,
Paul

--

 

[Shazaxacpcc]

Paul,

Thank you so much for you help. This will save me hours of manual work.

 

[PCLIVE]

You're welcome.

--

Paul,

Thank you so much for you help. This will save me hours of manual work.