|
List Info
Thread: Sumif formula
|
|
| Sumif formula |
|
2006-02-28 18:35:46 |
Bob, thank you so much for helping me. The formula works
great!
One more question: I noticed that with Sumproduct formula I
cannot use the
entire column (i.e. B:B) in the array, however B1:B13232
works fine. Is that
true?
Thanks,
Milana
-----Original Message-----
From: MS Excel General Q & A List [mailto:EXCEL-G PEACH.EASE.LSOFT.COM] On
Behalf Of Umlas, Robert
Sent: Tuesday, February 28, 2006 11:33 AM
To: EXCEL-GPEACH.EASE.LSOFT.COM
Subject: Re: Sumif formula
Sure - bobumlasyahoo.com
*** ANY TAX ADVICE IN THIS COMMUNICATION IS NOT INTENDED OR
WRITTEN BY KPMG
TO BE USED, AND CANNOT BE USED, BY A CLIENT OR ANY OTHER
PERSON OR ENTITY
FOR THE PURPOSE OF (i) AVOIDING PENALTIES THAT MAY BE
IMPOSED ON ANY
TAXPAYER OR (ii) PROMOTING, MARKETING OR RECOMMENDING TO
ANOTHER PARTY ANY
MATTERS ADDRESSED HEREIN. ***
Any advice in this communication is limited to the
conclusions specifically
set forth herein and is based on the completeness and
accuracy of the stated
facts, assumptions and/or representations included. In
rendering our advice,
we may consider tax authorities that are subject to change,
retroactively
and/or prospectively, and any such changes could affect the
validity of our
advice. We will not update our advice for subsequent changes
or
modifications to the law and regulations, or to the judicial
and
administrative interpretations thereof.
-----Original Message-----
From: MS Excel General Q & A List [mailto:EXCEL-GPEACH.EASE.LSOFT.COM]
On Behalf Of Shtern Milana
Sent: Tuesday, February 28, 2006 11:09 AM
To: EXCEL-GPEACH.EASE.LSOFT.COM
Subject: Re: Sumif formula
Bob,
I am doing a bad job of explaining my situation. (Although,
now I understand
the use of N). Is it possible for me to email you a simple
example offline?
Thank you,
Milana
-----Original Message-----
From: MS Excel General Q & A List [mailto:EXCEL-GPEACH.EASE.LSOFT.COM]
On
Behalf Of Umlas, Robert
Sent: Tuesday, February 28, 2006 10:59 AM
To: EXCEL-GPEACH.EASE.LSOFT.COM
Subject: Re: Sumif formula
N is a builtin function which changes true to 1, false to 0,
text to 0,
numbers stay as they are.
N(A1:A100="Apples") becomes a series of 1's
& 0's.
N(B1:B100="Oranges") becomes a series of 1's
& 0's.
C1:C100 stay as they are.
So you have something like
=SUMPRODUCT({1,1,0,0,0,...},{0,1,0,1,1,...},{123,234,345,456
,567...})
Which becomes =SUMPRODUCT({0,234,0,0,0,...}) based in this
silly example, or
234. If you already have applesoranges in a range, say
D1 100,
then you can
use either
=SUMIF(D1100,&qu
ot;applesoranges",C1:C100)
Or
=SUMPRODUCT(N(D1100=&qu
ot;applesoranges"),C1:C100)
*** ANY TAX ADVICE IN THIS COMMUNICATION IS NOT INTENDED OR
WRITTEN BY KPMG
TO BE USED, AND CANNOT BE USED, BY A CLIENT OR ANY OTHER
PERSON OR ENTITY
FOR THE PURPOSE OF (i) AVOIDING PENALTIES THAT MAY BE
IMPOSED ON ANY
TAXPAYER OR (ii) PROMOTING, MARKETING OR RECOMMENDING TO
ANOTHER PARTY ANY
MATTERS ADDRESSED HEREIN. ***
Any advice in this communication is limited to the
conclusions specifically
set forth herein and is based on the completeness and
accuracy of the stated
facts, assumptions and/or representations included. In
rendering our advice,
we may consider tax authorities that are subject to change,
retroactively
and/or prospectively, and any such changes could affect the
validity of our
advice. We will not update our advice for subsequent changes
or
modifications to the law and regulations, or to the judicial
and
administrative interpretations thereof.
-----Original Message-----
From: MS Excel General Q & A List [mailto:EXCEL-GPEACH.EASE.LSOFT.COM]
On Behalf Of Shtern Milana
Sent: Tuesday, February 28, 2006 10:52 AM
To: EXCEL-GPEACH.EASE.LSOFT.COM
Subject: Re: Sumif formula
Thank you Bob, but can you explain to me the use of N?
Also, is it possible (I am sure it is, but I am just not
understanding the
formula yet) to reference a cell where I already have the
concatenated
ApplesOranges. Meaning, I have a concatenated list of values
that I need to
look up in this range.
I hope I am making sense here.
Thanks,
Milana
-----Original Message-----
From: MS Excel General Q & A List [mailto:EXCEL-GPEACH.EASE.LSOFT.COM]
On
Behalf Of Umlas, Robert
Sent: Tuesday, February 28, 2006 10:40 AM
To: EXCEL-GPEACH.EASE.LSOFT.COM
Subject: Re: Sumif formula
Sure -- but not SUMIF. SUMPRODUCT:
=SUMPRODUCT(N(A1:A100="Apples"),N(B1:B100="
;Oranges"),C1:C100)
This will add up all column C where A&B=ApplesOranges
Bob Umlas
Excel MVP
*** ANY TAX ADVICE IN THIS COMMUNICATION IS NOT INTENDED OR
WRITTEN BY KPMG
TO BE USED, AND CANNOT BE USED, BY A CLIENT OR ANY OTHER
PERSON OR ENTITY
FOR THE PURPOSE OF (i) AVOIDING PENALTIES THAT MAY BE
IMPOSED ON ANY
TAXPAYER OR (ii) PROMOTING, MARKETING OR RECOMMENDING TO
ANOTHER PARTY ANY
MATTERS ADDRESSED HEREIN. ***
Any advice in this communication is limited to the
conclusions specifically
set forth herein and is based on the completeness and
accuracy of the stated
facts, assumptions and/or representations included. In
rendering our advice,
we may consider tax authorities that are subject to change,
retroactively
and/or prospectively, and any such changes could affect the
validity of our
advice. We will not update our advice for subsequent changes
or
modifications to the law and regulations, or to the judicial
and
administrative interpretations thereof.
-----Original Message-----
From: MS Excel General Q & A List [mailto:EXCEL-GPEACH.EASE.LSOFT.COM]
On Behalf Of Shtern Milana
Sent: Tuesday, February 28, 2006 10:34 AM
To: EXCEL-GPEACH.EASE.LSOFT.COM
Subject: Sumif formula
Hello,
I don't think this is possible, but you all are much wiser
than me and will
know of a solution.
I am looking to do a Sumif type formula to look for a
specific combination
of fields. Since this combination doesn't exist in any one
cell in the
range, I would typically create a column to concatenate that
combination and
then do a sumif. However, in this case I would like to avoid
creating that
column.
Is it possible to concatenate the entire range within Sumif
formula?
Ex:
The range currently looks like this:
Apples Oranges
I want the range to look like this without creating an extra
column:
ApplesOranges
CONFIDENTIALITY: This communication, including attachments,
is for
exclusive use of the addressee(s) and may contain
proprietary, confidential
or privileged information. If you are not the intended
recipient, any use,
copying, disclosure, or distribution or the taking of any
action in reliance
upon this information is strictly prohibited. If you are not
the intended
recipient, please notify the sender immediately and delete
this
communication and destroy all copies.
------------------------------------------------------------
------------
--
The EXCEL-G list is hosted on a Windows NT(TM) machine
running L-Soft
international's LISTSERV(R) software. For
subscription/signoff info and
archives, see htt
p://peach.ease.lsoft.com/archives/excel-g.html .
COPYRIGHT INFO:
http://peach.ease.lsoft.com/scripts/wa
.exe?SHOWTPL=COPYRIGHT&L=EXCEL-G
************************************************************
************
****
*
The information in this email is confidential and may be
legally privileged.
It is intended solely for the addressee. Access to this
email by anyone else
is unauthorized.
If you are not the intended recipient, any disclosure,
copying, distribution
or any action taken or omitted to be taken in reliance on
it, is prohibited
and may be unlawful. When addressed to our clients any
opinions or advice
contained in this email are subject to the terms and
conditions expressed in
the governing KPMG client engagement letter.
************************************************************
************
****
*
------------------------------------------------------------
------------
--
The EXCEL-G list is hosted on a Windows NT(TM) machine
running L-Soft
international's LISTSERV(R) software. For
subscription/signoff info and
archives, see htt
p://peach.ease.lsoft.com/archives/excel-g.html .
COPYRIGHT INFO:
http://peach.ease.lsoft.com/scripts/wa
.exe?SHOWTPL=COPYRIGHT&L=EXCEL-G
------------------------------------------------------------
------------
--
The EXCEL-G list is hosted on a Windows NT(TM) machine
running L-Soft
international's LISTSERV(R) software. For
subscription/signoff info and
archives, see htt
p://peach.ease.lsoft.com/archives/excel-g.html .
COPYRIGHT INFO:
http://peach.ease.lsoft.com/scripts/wa
.exe?SHOWTPL=COPYRIGHT&L=EXCEL-G
************************************************************
************
****
*
The information in this email is confidential and may be
legally privileged.
It is intended solely for the addressee. Access to this
email by anyone else
is unauthorized.
If you are not the intended recipient, any disclosure,
copying, distribution
or any action taken or omitted to be taken in reliance on
it, is prohibited
and may be unlawful. When addressed to our clients any
opinions or advice
contained in this email are subject to the terms and
conditions expressed in
the governing KPMG client engagement letter.
************************************************************
************
****
*
------------------------------------------------------------
------------
--
The EXCEL-G list is hosted on a Windows NT(TM) machine
running L-Soft
international's LISTSERV(R) software. For
subscription/signoff info and
archives, see htt
p://peach.ease.lsoft.com/archives/excel-g.html .
COPYRIGHT INFO:
http://peach.ease.lsoft.com/scripts/wa
.exe?SHOWTPL=COPYRIGHT&L=EXCEL-G
------------------------------------------------------------
------------
--
The EXCEL-G list is hosted on a Windows NT(TM) machine
running L-Soft
international's LISTSERV(R) software. For
subscription/signoff info and
archives, see htt
p://peach.ease.lsoft.com/archives/excel-g.html .
COPYRIGHT INFO:
http://peach.ease.lsoft.com/scripts/wa
.exe?SHOWTPL=COPYRIGHT&L=EXCEL-G
************************************************************
****************
*
The information in this email is confidential and may be
legally privileged.
It is intended solely for the addressee. Access to this
email by anyone else
is unauthorized.
If you are not the intended recipient, any disclosure,
copying, distribution
or any action taken or omitted to be taken in reliance on
it, is prohibited
and may be unlawful. When addressed to our clients any
opinions or advice
contained in this email are subject to the terms and
conditions expressed in
the governing KPMG client engagement letter.
************************************************************
****************
*
------------------------------------------------------------
--------------
The EXCEL-G list is hosted on a Windows NT(TM) machine
running L-Soft
international's LISTSERV(R) software. For
subscription/signoff info and
archives, see htt
p://peach.ease.lsoft.com/archives/excel-g.html .
COPYRIGHT INFO:
http://peach.ease.lsoft.com/scripts/wa
.exe?SHOWTPL=COPYRIGHT&L=EXCEL-G
------------------------------------------------------------
--------------
The EXCEL-G list is hosted on a Windows NT(TM) machine
running L-Soft
international's LISTSERV(R) software. For
subscription/signoff info
and archives, see htt
p://peach.ease.lsoft.com/archives/excel-g.html .
COPYRIGHT INFO:
http://peach.ease.lsoft.com/scripts/wa
.exe?SHOWTPL=COPYRIGHT&L=EXCEL-G
|
|
| Sumif formula |
|
2006-02-28 18:58:52 |
Yes, array formula and SUMPRODUCT do not use whole columns.
-----Original Message-----
From: MS Excel General Q & A List [mailto:EXCEL-GPEACH.EASE.LSOFT.COM]
On Behalf Of Shtern Milana
Sent: 28 February 2006 18:36
To: EXCEL-GPEACH.EASE.LSOFT.COM
Subject: Re: Sumif formula
Bob, thank you so much for helping me. The formula works
great!
One more question: I noticed that with Sumproduct formula I
cannot use
the entire column (i.e. B:B) in the array, however B1:B13232
works fine.
Is that true?
Thanks,
Milana
-----Original Message-----
From: MS Excel General Q & A List [mailto:EXCEL-GPEACH.EASE.LSOFT.COM]
On Behalf Of Umlas, Robert
Sent: Tuesday, February 28, 2006 11:33 AM
To: EXCEL-GPEACH.EASE.LSOFT.COM
Subject: Re: Sumif formula
Sure - bobumlasyahoo.com
*** ANY TAX ADVICE IN THIS COMMUNICATION IS NOT INTENDED OR
WRITTEN BY
KPMG TO BE USED, AND CANNOT BE USED, BY A CLIENT OR ANY
OTHER PERSON OR
ENTITY FOR THE PURPOSE OF (i) AVOIDING PENALTIES THAT MAY BE
IMPOSED ON
ANY TAXPAYER OR (ii) PROMOTING, MARKETING OR RECOMMENDING TO
ANOTHER
PARTY ANY MATTERS ADDRESSED HEREIN. ***
Any advice in this communication is limited to the
conclusions
specifically set forth herein and is based on the
completeness and
accuracy of the stated facts, assumptions and/or
representations
included. In rendering our advice, we may consider tax
authorities that
are subject to change, retroactively and/or prospectively,
and any such
changes could affect the validity of our advice. We will not
update our
advice for subsequent changes or modifications to the law
and
regulations, or to the judicial and administrative
interpretations
thereof.
-----Original Message-----
From: MS Excel General Q & A List [mailto:EXCEL-GPEACH.EASE.LSOFT.COM]
On Behalf Of Shtern Milana
Sent: Tuesday, February 28, 2006 11:09 AM
To: EXCEL-GPEACH.EASE.LSOFT.COM
Subject: Re: Sumif formula
Bob,
I am doing a bad job of explaining my situation. (Although,
now I
understand the use of N). Is it possible for me to email you
a simple
example offline?
Thank you,
Milana
-----Original Message-----
From: MS Excel General Q & A List [mailto:EXCEL-GPEACH.EASE.LSOFT.COM]
On
Behalf Of Umlas, Robert
Sent: Tuesday, February 28, 2006 10:59 AM
To: EXCEL-GPEACH.EASE.LSOFT.COM
Subject: Re: Sumif formula
N is a builtin function which changes true to 1, false to 0,
text to 0,
numbers stay as they are.
N(A1:A100="Apples") becomes a series of 1's
& 0's.
N(B1:B100="Oranges") becomes a series of 1's
& 0's.
C1:C100 stay as they are.
So you have something like
=SUMPRODUCT({1,1,0,0,0,...},{0,1,0,1,1,...},{123,234,345,456
,567...})
Which becomes =SUMPRODUCT({0,234,0,0,0,...}) based in this
silly
example, or 234. If you already have applesoranges in a
range, say
D1100,
then you can use either
=SUMIF(D1100,&qu
ot;applesoranges",C1:C100)
Or
=SUMPRODUCT(N(D1100=&qu
ot;applesoranges"),C1:C100)
*** ANY TAX ADVICE IN THIS COMMUNICATION IS NOT INTENDED OR
WRITTEN BY
KPMG TO BE USED, AND CANNOT BE USED, BY A CLIENT OR ANY
OTHER PERSON OR
ENTITY FOR THE PURPOSE OF (i) AVOIDING PENALTIES THAT MAY BE
IMPOSED ON
ANY TAXPAYER OR (ii) PROMOTING, MARKETING OR RECOMMENDING TO
ANOTHER
PARTY ANY MATTERS ADDRESSED HEREIN. ***
Any advice in this communication is limited to the
conclusions
specifically set forth herein and is based on the
completeness and
accuracy of the stated facts, assumptions and/or
representations
included. In rendering our advice, we may consider tax
authorities that
are subject to change, retroactively and/or prospectively,
and any such
changes could affect the validity of our advice. We will not
update our
advice for subsequent changes or modifications to the law
and
regulations, or to the judicial and administrative
interpretations
thereof.
-----Original Message-----
From: MS Excel General Q & A List [mailto:EXCEL-GPEACH.EASE.LSOFT.COM]
On Behalf Of Shtern Milana
Sent: Tuesday, February 28, 2006 10:52 AM
To: EXCEL-GPEACH.EASE.LSOFT.COM
Subject: Re: Sumif formula
Thank you Bob, but can you explain to me the use of N?
Also, is it possible (I am sure it is, but I am just not
understanding
the formula yet) to reference a cell where I already have
the
concatenated ApplesOranges. Meaning, I have a concatenated
list of
values that I need to look up in this range.
I hope I am making sense here.
Thanks,
Milana
-----Original Message-----
From: MS Excel General Q & A List [mailto:EXCEL-GPEACH.EASE.LSOFT.COM]
On
Behalf Of Umlas, Robert
Sent: Tuesday, February 28, 2006 10:40 AM
To: EXCEL-GPEACH.EASE.LSOFT.COM
Subject: Re: Sumif formula
Sure -- but not SUMIF. SUMPRODUCT:
=SUMPRODUCT(N(A1:A100="Apples"),N(B1:B100="
;Oranges"),C1:C100)
This will add up all column C where A&B=ApplesOranges
Bob Umlas
Excel MVP
*** ANY TAX ADVICE IN THIS COMMUNICATION IS NOT INTENDED OR
WRITTEN BY
KPMG TO BE USED, AND CANNOT BE USED, BY A CLIENT OR ANY
OTHER PERSON OR
ENTITY FOR THE PURPOSE OF (i) AVOIDING PENALTIES THAT MAY BE
IMPOSED ON
ANY TAXPAYER OR (ii) PROMOTING, MARKETING OR RECOMMENDING TO
ANOTHER
PARTY ANY MATTERS ADDRESSED HEREIN. ***
Any advice in this communication is limited to the
conclusions
specifically set forth herein and is based on the
completeness and
accuracy of the stated facts, assumptions and/or
representations
included. In rendering our advice, we may consider tax
authorities that
are subject to change, retroactively and/or prospectively,
and any such
changes could affect the validity of our advice. We will not
update our
advice for subsequent changes or modifications to the law
and
regulations, or to the judicial and administrative
interpretations
thereof.
-----Original Message-----
From: MS Excel General Q & A List [mailto:EXCEL-GPEACH.EASE.LSOFT.COM]
On Behalf Of Shtern Milana
Sent: Tuesday, February 28, 2006 10:34 AM
To: EXCEL-GPEACH.EASE.LSOFT.COM
Subject: Sumif formula
Hello,
I don't think this is possible, but you all are much wiser
than me and
will know of a solution.
I am looking to do a Sumif type formula to look for a
specific
combination of fields. Since this combination doesn't exist
in any one
cell in the range, I would typically create a column to
concatenate that
combination and then do a sumif. However, in this case I
would like to
avoid creating that column.
Is it possible to concatenate the entire range within Sumif
formula?
Ex:
The range currently looks like this:
Apples Oranges
I want the range to look like this without creating an extra
column:
ApplesOranges
CONFIDENTIALITY: This communication, including attachments,
is for
exclusive use of the addressee(s) and may contain
proprietary,
confidential or privileged information. If you are not the
intended
recipient, any use, copying, disclosure, or distribution or
the taking
of any action in reliance upon this information is strictly
prohibited.
If you are not the intended recipient, please notify the
sender
immediately and delete this communication and destroy all
copies.
------------------------------------------------------------
------------
--
The EXCEL-G list is hosted on a Windows NT(TM) machine
running L-Soft
international's LISTSERV(R) software. For
subscription/signoff info and
archives, see htt
p://peach.ease.lsoft.com/archives/excel-g.html .
COPYRIGHT INFO:
http://peach.ease.lsoft.com/scripts/wa
.exe?SHOWTPL=COPYRIGHT&L=EXCEL-G
************************************************************
************
****
*
The information in this email is confidential and may be
legally
privileged. It is intended solely for the addressee. Access
to this
email by anyone else is unauthorized.
If you are not the intended recipient, any disclosure,
copying,
distribution or any action taken or omitted to be taken in
reliance on
it, is prohibited and may be unlawful. When addressed to our
clients any
opinions or advice contained in this email are subject to
the terms and
conditions expressed in the governing KPMG client engagement
letter.
************************************************************
************
****
*
------------------------------------------------------------
------------
--
The EXCEL-G list is hosted on a Windows NT(TM) machine
running L-Soft
international's LISTSERV(R) software. For
subscription/signoff info and
archives, see htt
p://peach.ease.lsoft.com/archives/excel-g.html .
COPYRIGHT INFO:
http://peach.ease.lsoft.com/scripts/wa
.exe?SHOWTPL=COPYRIGHT&L=EXCEL-G
------------------------------------------------------------
------------
--
The EXCEL-G list is hosted on a Windows NT(TM) machine
running L-Soft
international's LISTSERV(R) software. For
subscription/signoff info and
archives, see htt
p://peach.ease.lsoft.com/archives/excel-g.html .
COPYRIGHT INFO:
http://peach.ease.lsoft.com/scripts/wa
.exe?SHOWTPL=COPYRIGHT&L=EXCEL-G
************************************************************
************
****
*
The information in this email is confidential and may be
legally
privileged. It is intended solely for the addressee. Access
to this
email by anyone else is unauthorized.
If you are not the intended recipient, any disclosure,
copying,
distribution or any action taken or omitted to be taken in
reliance on
it, is prohibited and may be unlawful. When addressed to our
clients any
opinions or advice contained in this email are subject to
the terms and
conditions expressed in the governing KPMG client engagement
letter.
************************************************************
************
****
*
------------------------------------------------------------
------------
--
The EXCEL-G list is hosted on a Windows NT(TM) machine
running L-Soft
international's LISTSERV(R) software. For
subscription/signoff info and
archives, see htt
p://peach.ease.lsoft.com/archives/excel-g.html .
COPYRIGHT INFO:
http://peach.ease.lsoft.com/scripts/wa
.exe?SHOWTPL=COPYRIGHT&L=EXCEL-G
------------------------------------------------------------
------------
--
The EXCEL-G list is hosted on a Windows NT(TM) machine
running L-Soft
international's LISTSERV(R) software. For
subscription/signoff info and
archives, see htt
p://peach.ease.lsoft.com/archives/excel-g.html .
COPYRIGHT INFO:
http://peach.ease.lsoft.com/scripts/wa
.exe?SHOWTPL=COPYRIGHT&L=EXCEL-G
************************************************************
************
****
*
The information in this email is confidential and may be
legally
privileged. It is intended solely for the addressee. Access
to this
email by anyone else is unauthorized.
If you are not the intended recipient, any disclosure,
copying,
distribution or any action taken or omitted to be taken in
reliance on
it, is prohibited and may be unlawful. When addressed to our
clients any
opinions or advice contained in this email are subject to
the terms and
conditions expressed in the governing KPMG client engagement
letter.
************************************************************
************
****
*
------------------------------------------------------------
------------
--
The EXCEL-G list is hosted on a Windows NT(TM) machine
running L-Soft
international's LISTSERV(R) software. For
subscription/signoff info and
archives, see htt
p://peach.ease.lsoft.com/archives/excel-g.html .
COPYRIGHT INFO:
http://peach.ease.lsoft.com/scripts/wa
.exe?SHOWTPL=COPYRIGHT&L=EXCEL-G
------------------------------------------------------------
------------
--
The EXCEL-G list is hosted on a Windows NT(TM) machine
running L-Soft
international's LISTSERV(R) software. For
subscription/signoff info and
archives, see htt
p://peach.ease.lsoft.com/archives/excel-g.html .
COPYRIGHT INFO:
http://peach.ease.lsoft.com/scripts/wa
.exe?SHOWTPL=COPYRIGHT&L=EXCEL-G
--
No virus found in this incoming message.
Checked by AVG Free Edition.
Version: 7.1.375 / Virus Database: 268.1.1/270 - Release
Date:
27/02/2006
--
No virus found in this outgoing message.
Checked by AVG Free Edition.
Version: 7.1.375 / Virus Database: 268.1.1/270 - Release
Date:
27/02/2006
------------------------------------------------------------
--------------
The EXCEL-G list is hosted on a Windows NT(TM) machine
running L-Soft
international's LISTSERV(R) software. For
subscription/signoff info
and archives, see htt
p://peach.ease.lsoft.com/archives/excel-g.html .
COPYRIGHT INFO:
http://peach.ease.lsoft.com/scripts/wa
.exe?SHOWTPL=COPYRIGHT&L=EXCEL-G
|
|
[1-2]
|
|