Hello,
My problem is that I have data in four columns. First column has the data
which is mixed of text and numbers (Eg. KAM-1000), second column has date
(Eg. 23-May-2007), third column has text that is different types of product
name, last column is the price of the products. I want to sum up the last
column if all the three columns matches three different criteria. I am giving
an example of the data below:
ID Date Product Name Price
KAM-1000 23-May-07 Apple 400
KAM-1001 24-MAY-07 Orange 100
KAM-2000 25-MAY-07 Grape 200
KAM-1000 23-May-07 Apple 300
Now I want the function to sum up the row 1 and 2 and give a result of 700.
I used this formula but it resulted 0:
=SUMPRODUCT($A$221:$A$224="KAM-1736",$G$221:$G$224="Business Solutions -
Dhaka",--(TEXT($E$221:$E$224,"yyyymmmdd")="2007May23"),$M$2 21:$M$224)
What should I do? Is there any other function which will give me the
accurate result?

Thanking you in advance

Hi

You need to include each term inside a set of parentheses, apart from
your final values which are being summed, and use the double unary minus
to coerce each of the True/False to 0/1
Try
=SUMPRODUCT((--$A$221:$A$224="KAM-1736"),
--($G$221:$G$224="Business Solutions - Dhaka"),
--(TEXT($E$221:$E$224,"yyyymmmdd")="2007May23"),
$M$221:$M$224)


I have split it onto several lines so the NG reader doesn't break it at
an awkward point.
It is all one continuous formula
--
Regards

Roger Govier


"sadat" wrote in message
...
Hello,
My problem is that I have data in four columns. First column has the
data
which is mixed of text and numbers (Eg. KAM-1000), second column has
date
(Eg. 23-May-2007), third column has text that is different types of
product
name, last column is the price of the products. I want to sum up the
last
column if all the three columns matches three different criteria. I am
giving
an example of the data below:
ID Date Product Name Price
KAM-1000 23-May-07 Apple 400
KAM-1001 24-MAY-07 Orange 100
KAM-2000 25-MAY-07 Grape 200
KAM-1000 23-May-07 Apple 300
Now I want the function to sum up the row 1 and 2 and give a result of
700.
I used this formula but it resulted 0:
=SUMPRODUCT($A$221:$A$224="KAM-1736",$G$221:$G$224="Business
Solutions -
Dhaka",--(TEXT($E$221:$E$224,"yyyymmmdd")="2007May23"),$M$2 21:$M$224)
What should I do? Is there any other function which will give me the
accurate result?

Thanking you in advance

You've used the double unary minus to transform one of your booleans to a
number, but you may need to do the same for your other two boolean
expressions.

Also, if your example is representative, your dates may not really be dates,
but may be text that looks like dates. You can check by temporarily
changing the format of your date cells to general or number, and see whether
23-May-07 becomes 39225, etc. If they don't change format, they're
presumably text, not dates, so your test won't evaluate appropriately.

If you're still struggling, add helper columns with each test condition
separately, so that you can see which tests are passing and which failing.
--
David Biddulph

"sadat" wrote in message
...
Hello,
My problem is that I have data in four columns. First column has the data
which is mixed of text and numbers (Eg. KAM-1000), second column has date
(Eg. 23-May-2007), third column has text that is different types of
product
name, last column is the price of the products. I want to sum up the last
column if all the three columns matches three different criteria. I am
giving
an example of the data below:
ID Date Product Name Price
KAM-1000 23-May-07 Apple 400
KAM-1001 24-MAY-07 Orange 100
KAM-2000 25-MAY-07 Grape 200
KAM-1000 23-May-07 Apple 300
Now I want the function to sum up the row 1 and 2 and give a result of
700.
I used this formula but it resulted 0:
=SUMPRODUCT($A$221:$A$224="KAM-1736",$G$221:$G$224="Business Solutions -
Dhaka",--(TEXT($E$221:$E$224,"yyyymmmdd")="2007May23"),$M$2 21:$M$224)
What should I do? Is there any other function which will give me the
accurate result?

Thanking you in advance

Hi David

If they don't change format, they're presumably text, not dates, so
your test won't evaluate appropriately

Just a quick "heads up"
Whether it is an Excel date or Text '23 May 2007, the Text function
returns 2007May23

--
Regards

Roger Govier


"David Biddulph" <groups [at] biddulph.org.uk wrote in message
...
You've used the double unary minus to transform one of your booleans
to a number, but you may need to do the same for your other two
boolean expressions.

Also, if your example is representative, your dates may not really be
dates, but may be text that looks like dates. You can check by
temporarily changing the format of your date cells to general or
number, and see whether 23-May-07 becomes 39225, etc. If they don't
change format, they're presumably text, not dates, so your test won't
evaluate appropriately.

If you're still struggling, add helper columns with each test
condition separately, so that you can see which tests are passing and
which failing.
--
David Biddulph

"sadat" wrote in message
...
Hello,
My problem is that I have data in four columns. First column has the
data
which is mixed of text and numbers (Eg. KAM-1000), second column has
date
(Eg. 23-May-2007), third column has text that is different types of
product
name, last column is the price of the products. I want to sum up the
last
column if all the three columns matches three different criteria. I
am giving
an example of the data below:
ID Date Product Name Price
KAM-1000 23-May-07 Apple 400
KAM-1001 24-MAY-07 Orange 100
KAM-2000 25-MAY-07 Grape 200
KAM-1000 23-May-07 Apple 300
Now I want the function to sum up the row 1 and 2 and give a result
of 700.
I used this formula but it resulted 0:
=SUMPRODUCT($A$221:$A$224="KAM-1736",$G$221:$G$224="Business
Solutions -
Dhaka",--(TEXT($E$221:$E$224,"yyyymmmdd")="2007May23"),$M$2 21:$M$224)
What should I do? Is there any other function which will give me the
accurate result?

Thanking you in advance

But if the text is (for example)
23-may-07 , (with the spaces at the beginning),
your TEXT() function returns
23-may-07 , not
2007May23

It is often spaces (or other unprintable characters) that cause text to
appear valid at first glance, but not to be accepted by functions looking
for specific formats.
--
David Biddulph

"Roger Govier" wrote in message
...
Hi David

If they don't change format, they're presumably text, not dates, so your
test won't evaluate appropriately

Just a quick "heads up"
Whether it is an Excel date or Text '23 May 2007, the Text function
returns 2007May23

--
Regards

Roger Govier

"David Biddulph" <groups [at] biddulph.org.uk wrote in message
...
You've used the double unary minus to transform one of your booleans to a
number, but you may need to do the same for your other two boolean
expressions.

Also, if your example is representative, your dates may not really be
dates, but may be text that looks like dates. You can check by
temporarily changing the format of your date cells to general or number,
and see whether 23-May-07 becomes 39225, etc. If they don't change
format, they're presumably text, not dates, so your test won't evaluate
appropriately.

If you're still struggling, add helper columns with each test condition
separately, so that you can see which tests are passing and which
failing.
--
David Biddulph

"sadat" wrote in message
...
Hello,
My problem is that I have data in four columns. First column has the
data
which is mixed of text and numbers (Eg. KAM-1000), second column has
date
(Eg. 23-May-2007), third column has text that is different types of
product
name, last column is the price of the products. I want to sum up the
last
column if all the three columns matches three different criteria. I am
giving
an example of the data below:
ID Date Product Name Price
KAM-1000 23-May-07 Apple 400
KAM-1001 24-MAY-07 Orange 100
KAM-2000 25-MAY-07 Grape 200
KAM-1000 23-May-07 Apple 300
Now I want the function to sum up the row 1 and 2 and give a result of
700.
I used this formula but it resulted 0:
=SUMPRODUCT($A$221:$A$224="KAM-1736",$G$221:$G$224="Business Solutions -
Dhaka",--(TEXT($E$221:$E$224,"yyyymmmdd")="2007May23"),$M$2 21:$M$224)
What should I do? Is there any other function which will give me the
accurate result?

Thanking you in advance

How right you are, David.
My apologies, I had not considered leading characters. <very red
face!!!

--
Regards

Roger Govier


"David Biddulph" <groups [at] biddulph.org.uk wrote in message
...
But if the text is (for example)
23-may-07 , (with the spaces at the beginning),
your TEXT() function returns
23-may-07 , not
2007May23

It is often spaces (or other unprintable characters) that cause text
to appear valid at first glance, but not to be accepted by functions
looking for specific formats.
--
David Biddulph

"Roger Govier" wrote in message
...
Hi David

If they don't change format, they're presumably text, not dates, so
your test won't evaluate appropriately

Just a quick "heads up"
Whether it is an Excel date or Text '23 May 2007, the Text function
returns 2007May23

--
Regards

Roger Govier

"David Biddulph" <groups [at] biddulph.org.uk wrote in message
...
You've used the double unary minus to transform one of your booleans
to a number, but you may need to do the same for your other two
boolean expressions.

Also, if your example is representative, your dates may not really
be dates, but may be text that looks like dates. You can check by
temporarily changing the format of your date cells to general or
number, and see whether 23-May-07 becomes 39225, etc. If they don't
change format, they're presumably text, not dates, so your test
won't evaluate appropriately.

If you're still struggling, add helper columns with each test
condition separately, so that you can see which tests are passing
and which failing.
--
David Biddulph

"sadat" wrote in message
...
Hello,
My problem is that I have data in four columns. First column has
the data
which is mixed of text and numbers (Eg. KAM-1000), second column
has date
(Eg. 23-May-2007), third column has text that is different types of
product
name, last column is the price of the products. I want to sum up
the last
column if all the three columns matches three different criteria. I
am giving
an example of the data below:
ID Date Product Name Price
KAM-1000 23-May-07 Apple 400
KAM-1001 24-MAY-07 Orange 100
KAM-2000 25-MAY-07 Grape 200
KAM-1000 23-May-07 Apple 300
Now I want the function to sum up the row 1 and 2 and give a result
of 700.
I used this formula but it resulted 0:
=SUMPRODUCT($A$221:$A$224="KAM-1736",$G$221:$G$224="Business
Solutions -
Dhaka",--(TEXT($E$221:$E$224,"yyyymmmdd")="2007May23"),$M$2 21:$M$224)
What should I do? Is there any other function which will give me
the
accurate result?

Thanking you in advance

Try adjusting your formula to:

=SUMPRODUCT(--($A$221:$A$224="KAM-1736"),--($G$221:$G$224="Business Solutions -
Dhaka"),--(TEXT($E$221:$E$224,"yyyymmmdd")="2007May23"),$M$2 21:$M$224)

Dear Roger:
your solution is very much helpful. but threre is a problem, the function do
not understand both text and number (Eg. KAM-1000). If I change that column
and add only the number (Eg. 1000) then the function recognizes that. How can
I change the formula so that it can recognize both the text and number?

Best regards,
Sadat

"Roger Govier" wrote:

Hi

You need to include each term inside a set of parentheses, apart from
your final values which are being summed, and use the double unary minus
to coerce each of the True/False to 0/1
Try
=SUMPRODUCT((--$A$221:$A$224="KAM-1736"),
--($G$221:$G$224="Business Solutions - Dhaka"),
--(TEXT($E$221:$E$224,"yyyymmmdd")="2007May23"),
$M$221:$M$224)


I have split it onto several lines so the NG reader doesn't break it at
an awkward point.
It is all one continuous formula
--
Regards

Roger Govier


"sadat" wrote in message
...
Hello,
My problem is that I have data in four columns. First column has the
data
which is mixed of text and numbers (Eg. KAM-1000), second column has
date
(Eg. 23-May-2007), third column has text that is different types of
product
name, last column is the price of the products. I want to sum up the
last
column if all the three columns matches three different criteria. I am
giving
an example of the data below:
ID Date Product Name Price
KAM-1000 23-May-07 Apple 400
KAM-1001 24-MAY-07 Orange 100
KAM-2000 25-MAY-07 Grape 200
KAM-1000 23-May-07 Apple 300
Now I want the function to sum up the row 1 and 2 and give a result of
700.
I used this formula but it resulted 0:
=SUMPRODUCT($A$221:$A$224="KAM-1736",$G$221:$G$224="Business
Solutions -
Dhaka",--(TEXT($E$221:$E$224,"yyyymmmdd")="2007May23"),$M$2 21:$M$224)
What should I do? Is there any other function which will give me the
accurate result?

Thanking you in advance

Hi Sadat

I apologise. I did not notice when I copied your formula and amended it,
that you had the first set of double unary minus signs after the second
parenthesis, instead of before it.

Try
=SUMPRODUCT(--($A$221:$A$224="KAM-1736"),
--($G$221:$G$224="Business Solutions - Dhaka"),
--(TEXT($E$221:$E$224,"yyyymmmdd")="2007May23"),
$M$221:$M$224)


--
Regards

Roger Govier


"sadat" wrote in message
...
Dear Roger:
your solution is very much helpful. but threre is a problem, the
function do
not understand both text and number (Eg. KAM-1000). If I change that
column
and add only the number (Eg. 1000) then the function recognizes that.
How can
I change the formula so that it can recognize both the text and
number?

Best regards,
Sadat

"Roger Govier" wrote:

Hi

You need to include each term inside a set of parentheses, apart
from
your final values which are being summed, and use the double unary
minus
to coerce each of the True/False to 0/1
Try
=SUMPRODUCT((--$A$221:$A$224="KAM-1736"),
--($G$221:$G$224="Business Solutions - Dhaka"),
--(TEXT($E$221:$E$224,"yyyymmmdd")="2007May23"),
$M$221:$M$224)


I have split it onto several lines so the NG reader doesn't break it
at
an awkward point.
It is all one continuous formula
--
Regards

Roger Govier


"sadat" wrote in message
...
Hello,
My problem is that I have data in four columns. First column has
the
data
which is mixed of text and numbers (Eg. KAM-1000), second column
has
date
(Eg. 23-May-2007), third column has text that is different types of
product
name, last column is the price of the products. I want to sum up
the
last
column if all the three columns matches three different criteria. I
am
giving
an example of the data below:
ID Date Product Name Price
KAM-1000 23-May-07 Apple 400
KAM-1001 24-MAY-07 Orange 100
KAM-2000 25-MAY-07 Grape 200
KAM-1000 23-May-07 Apple 300
Now I want the function to sum up the row 1 and 2 and give a result
of
700.
I used this formula but it resulted 0:
=SUMPRODUCT($A$221:$A$224="KAM-1736",$G$221:$G$224="Business
Solutions -
Dhaka",--(TEXT($E$221:$E$224,"yyyymmmdd")="2007May23"),$M$2 21:$M$224)
What should I do? Is there any other function which will give me
the
accurate result?

Thanking you in advance