Excel Formulas with multiple criteria
 

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

Excel Formulas with multiple criteria
 

Hi,

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

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

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

Mike

"Shazaxacpcc" wrote:

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

Excel Formulas with multiple criteria
 

Try SUMPRODUCT:

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

HTH,
Paul

--

"Shazaxacpcc" wrote in message
...
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

Excel Formulas with multiple criteria
 

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" wrote:

Hi,

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

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

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

Mike

"Shazaxacpcc" wrote:

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

Excel Formulas with multiple criteria
 

It then becomes a mix of the 2


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

Mike

"Shazaxacpcc" wrote:

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" wrote:

Hi,

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

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

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

Mike

"Shazaxacpcc" wrote:

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

Excel Formulas with multiple criteria
 

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" wrote:

Try SUMPRODUCT:

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

HTH,
Paul

--

"Shazaxacpcc" wrote in message
...
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

Excel Formulas with multiple criteria
 

Second criteria. Change the operator and remove the quotes.

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

Does that help?


--

"SUMSUE" wrote in message
...
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" wrote:

Try SUMPRODUCT:

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

HTH,
Paul

--

"Shazaxacpcc" wrote in message
...
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

Excel Formulas with multiple criteria
 

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

"PCLIVE" wrote:

Second criteria. Change the operator and remove the quotes.

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

Does that help?


--

"SUMSUE" wrote in message
...
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" wrote:

Try SUMPRODUCT:

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

HTH,
Paul

--

"Shazaxacpcc" wrote in message
...
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

Excel Formulas with multiple criteria
 

Try this:

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

Regards,
Paul

--

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

"PCLIVE" wrote:

Second criteria. Change the operator and remove the quotes.

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

Does that help?


--

"SUMSUE" wrote in message
...
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" wrote:

Try SUMPRODUCT:

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

HTH,
Paul

--

"Shazaxacpcc" wrote in message
...
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

Excel Formulas with multiple criteria
 

Paul,

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

"PCLIVE" wrote:

Try this:

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

Regards,
Paul

--

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

"PCLIVE" wrote:

Second criteria. Change the operator and remove the quotes.

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

Does that help?


--

"SUMSUE" wrote in message
...
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" wrote:

Try SUMPRODUCT:

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

HTH,
Paul

--

"Shazaxacpcc" wrote in message
...
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

Excel Formulas with multiple criteria
 

You're welcome.

--

"Shazaxacpcc" wrote in message
...
Paul,

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

"PCLIVE" wrote:

Try this:

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

Regards,
Paul

--

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

"PCLIVE" wrote:

Second criteria. Change the operator and remove the quotes.

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

Does that help?


--

"SUMSUE" wrote in message
...
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" wrote:

Try SUMPRODUCT:

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

HTH,
Paul

--

"Shazaxacpcc" wrote in
message
...
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