software

I'm trying to get the sum of a list of numbers that start with a certain
number (in this example 1). I can't seem to make any wildcard work. My
numbers all have 5 digits so I've tried 1* and 1????.
=SUMIF(A1:A307,quot;1*quot;,F1:F307)
=SUMIF(A1:A307,quot;1????quot;,F1:F307)
don't work. Will someone please tell me what I'm doing incorrectly? Thanks
in advance.

Hi

One way
=SUMPRODUCT(--(LEFT(A1:A307)=quot;1quot;),F1:F307)

--
Regards

Roger Govierquot;fractallindaquot; gt; wrote in message
...
gt; I'm trying to get the sum of a list of numbers that start with a
gt; certain
gt; number (in this example 1). I can't seem to make any wildcard work.
gt; My
gt; numbers all have 5 digits so I've tried 1* and 1????.
gt; =SUMIF(A1:A307,quot;1*quot;,F1:F307)
gt; =SUMIF(A1:A307,quot;1????quot;,F1:F307)
gt; don't work. Will someone please tell me what I'm doing incorrectly?
gt; Thanks
gt; in advance.
terrific!! It worked great!
What is the quot;--quot; ? I had tried that formula without -- and it didn't work.
Thank you SO much

quot;fractallindaquot; wrote:

gt; I'm trying to get the sum of a list of numbers that start with a certain
gt; number (in this example 1). I can't seem to make any wildcard work. My
gt; numbers all have 5 digits so I've tried 1* and 1????.
gt; =SUMIF(A1:A307,quot;1*quot;,F1:F307)
gt; =SUMIF(A1:A307,quot;1????quot;,F1:F307)
gt; don't work. Will someone please tell me what I'm doing incorrectly? Thanks
gt; in advance.

Hi

The comparison on column A will return True or False. In order to
convert theses values to 1 and 0, the double unary minus (--) is used to
coerce them, so the results will either be
1 * F Value or
0 * F Value
which then gets summed by Sumproduct.

--
Regards

Roger Govierquot;fractallindaquot; gt; wrote in message
...
gt; terrific!! It worked great!
gt; What is the quot;--quot; ? I had tried that formula without -- and it didn't
gt; work.
gt; Thank you SO much
gt;
gt; quot;fractallindaquot; wrote:
gt;
gt;gt; I'm trying to get the sum of a list of numbers that start with a
gt;gt; certain
gt;gt; number (in this example 1). I can't seem to make any wildcard work.
gt;gt; My
gt;gt; numbers all have 5 digits so I've tried 1* and 1????.
gt;gt; =SUMIF(A1:A307,quot;1*quot;,F1:F307)
gt;gt; =SUMIF(A1:A307,quot;1????quot;,F1:F307)
gt;gt; don't work. Will someone please tell me what I'm doing incorrectly?
gt;gt; Thanks
gt;gt; in advance.
the quot;--quot; is used to convert an array of logical values (ie {TRUE, TRUE,
FALSE, TRUE}), to an array of 1's and 0's (ie {1,1,0,1}). This is necessary
because SUMPRODUCT will skip anything other than numerical values (like the
SUM function).

quot;fractallindaquot; wrote:

gt; terrific!! It worked great!
gt; What is the quot;--quot; ? I had tried that formula without -- and it didn't work.
gt; Thank you SO much
gt;
gt; quot;fractallindaquot; wrote:
gt;
gt; gt; I'm trying to get the sum of a list of numbers that start with a certain
gt; gt; number (in this example 1). I can't seem to make any wildcard work. My
gt; gt; numbers all have 5 digits so I've tried 1* and 1????.
gt; gt; =SUMIF(A1:A307,quot;1*quot;,F1:F307)
gt; gt; =SUMIF(A1:A307,quot;1????quot;,F1:F307)
gt; gt; don't work. Will someone please tell me what I'm doing incorrectly? Thanks
gt; gt; in advance.

gt; What is the quot;--quot; ?

The comparison (LEFT(A1:A307)=quot;1quot;) returns an array of TRUE and
FALSE values. These need to be converted to their numeric
equivalent (1 and 0) so that they can be summed by SUMPRODUCT.
The double negation (--) takes the negative of the negative to
convert the TRUE and FALSE values to 1 and 0.--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting, LLC
www.cpearson.com
quot;fractallindaquot; gt; wrote in
message
...
gt; terrific!! It worked great!
gt; What is the quot;--quot; ? I had tried that formula without -- and it
gt; didn't work.
gt; Thank you SO much
gt;
gt; quot;fractallindaquot; wrote:
gt;
gt;gt; I'm trying to get the sum of a list of numbers that start with
gt;gt; a certain
gt;gt; number (in this example 1). I can't seem to make any wildcard
gt;gt; work. My
gt;gt; numbers all have 5 digits so I've tried 1* and 1????.
gt;gt; =SUMIF(A1:A307,quot;1*quot;,F1:F307)
gt;gt; =SUMIF(A1:A307,quot;1????quot;,F1:F307)
gt;gt; don't work. Will someone please tell me what I'm doing
gt;gt; incorrectly? Thanks
gt;gt; in advance.
many thanks!

quot;Roger Govierquot; wrote:

gt; Hi
gt;
gt; One way
gt; =SUMPRODUCT(--(LEFT(A1:A307)=quot;1quot;),F1:F307)
gt;
gt; --
gt; Regards
gt;
gt; Roger Govier
gt;
gt;
gt; quot;fractallindaquot; gt; wrote in message
gt; ...
gt; gt; I'm trying to get the sum of a list of numbers that start with a
gt; gt; certain
gt; gt; number (in this example 1). I can't seem to make any wildcard work.
gt; gt; My
gt; gt; numbers all have 5 digits so I've tried 1* and 1????.
gt; gt; =SUMIF(A1:A307,quot;1*quot;,F1:F307)
gt; gt; =SUMIF(A1:A307,quot;1????quot;,F1:F307)
gt; gt; don't work. Will someone please tell me what I'm doing incorrectly?
gt; gt; Thanks
gt; gt; in advance.
gt;
gt;
gt;

thank you very much

quot;Chip Pearsonquot; wrote:

gt; gt; What is the quot;--quot; ?
gt;
gt; The comparison (LEFT(A1:A307)=quot;1quot;) returns an array of TRUE and
gt; FALSE values. These need to be converted to their numeric
gt; equivalent (1 and 0) so that they can be summed by SUMPRODUCT.
gt; The double negation (--) takes the negative of the negative to
gt; convert the TRUE and FALSE values to 1 and 0.
gt;
gt;
gt; --
gt; Cordially,
gt; Chip Pearson
gt; Microsoft MVP - Excel
gt; Pearson Software Consulting, LLC
gt; www.cpearson.com
gt;
gt;
gt;
gt; quot;fractallindaquot; gt; wrote in
gt; message
gt; ...
gt; gt; terrific!! It worked great!
gt; gt; What is the quot;--quot; ? I had tried that formula without -- and it
gt; gt; didn't work.
gt; gt; Thank you SO much
gt; gt;
gt; gt; quot;fractallindaquot; wrote:
gt; gt;
gt; gt;gt; I'm trying to get the sum of a list of numbers that start with
gt; gt;gt; a certain
gt; gt;gt; number (in this example 1). I can't seem to make any wildcard
gt; gt;gt; work. My
gt; gt;gt; numbers all have 5 digits so I've tried 1* and 1????.
gt; gt;gt; =SUMIF(A1:A307,quot;1*quot;,F1:F307)
gt; gt;gt; =SUMIF(A1:A307,quot;1????quot;,F1:F307)
gt; gt;gt; don't work. Will someone please tell me what I'm doing
gt; gt;gt; incorrectly? Thanks
gt; gt;gt; in advance.
gt;
gt;
gt;

thank you!

quot;Slothquot; wrote:

gt; the quot;--quot; is used to convert an array of logical values (ie {TRUE, TRUE,
gt; FALSE, TRUE}), to an array of 1's and 0's (ie {1,1,0,1}). This is necessary
gt; because SUMPRODUCT will skip anything other than numerical values (like the
gt; SUM function).
gt;
gt; quot;fractallindaquot; wrote:
gt;
gt; gt; terrific!! It worked great!
gt; gt; What is the quot;--quot; ? I had tried that formula without -- and it didn't work.
gt; gt; Thank you SO much
gt; gt;
gt; gt; quot;fractallindaquot; wrote:
gt; gt;
gt; gt; gt; I'm trying to get the sum of a list of numbers that start with a certain
gt; gt; gt; number (in this example 1). I can't seem to make any wildcard work. My
gt; gt; gt; numbers all have 5 digits so I've tried 1* and 1????.
gt; gt; gt; =SUMIF(A1:A307,quot;1*quot;,F1:F307)
gt; gt; gt; =SUMIF(A1:A307,quot;1????quot;,F1:F307)
gt; gt; gt; don't work. Will someone please tell me what I'm doing incorrectly? Thanks
gt; gt; gt; in advance.

Hi,

I'm trying to do something really similar but can't get it working, I'd be
really grateful if someone could explain why.

I've got a list of tasks in column B, names of people working on those tasks
in column C, the amount of effort in column D and the deadline in column E
but this is complicated by the fact that some tasks are complete and so have
quot;Completequot; entered into the deadline column and some of the complete tasks
also include the date the task was completed in this column e.g. quot;Complete
02/2009quot;.

In column G I've got a list of the unique name values and I need to sum the
amount of effort corresponding to completed tasks for each name. I've tried a
coupe of different formulas (listed below) to do this but cannot get it to
work propertly:-

This works if I delete all the date info from the completed tasks so that
they only have quot;Completequot; in column E:

{=SUM(IF(($C$3:$C$26=G3)*($E$3:$E$26=quot;Completequot;),$ D$3:$D$26))}

I thought it would be easy to just add a quot;*quot; into the formula so that it
would sum all effort for any row where the value in column E starts with the
word quot;completequot;:

{=SUM(IF(($C$3:$C$26=G3)*($E$3:$E$26=quot;Complete*quot;), $D$3:$D$26))}

But this returns 0 for all anmes and I know this is not correct.

Following the logic above, I tried this:

{=SUM(--(IF(($C$3:$C$26=G3)*($E$3:$E$26=quot;Complete*quot;),$D$3: $D$26)))}

but this also returns 0 for all names.

What am I doing wrong!

Thanks,
Jim.

quot;Slothquot; wrote:

gt; the quot;--quot; is used to convert an array of logical values (ie {TRUE, TRUE,
gt; FALSE, TRUE}), to an array of 1's and 0's (ie {1,1,0,1}). This is necessary
gt; because SUMPRODUCT will skip anything other than numerical values (like the
gt; SUM function).
gt;
gt; quot;fractallindaquot; wrote:
gt;
gt; gt; terrific!! It worked great!
gt; gt; What is the quot;--quot; ? I had tried that formula without -- and it didn't work.
gt; gt; Thank you SO much
gt; gt;
gt; gt; quot;fractallindaquot; wrote:
gt; gt;
gt; gt; gt; I'm trying to get the sum of a list of numbers that start with a certain
gt; gt; gt; number (in this example 1). I can't seem to make any wildcard work. My
gt; gt; gt; numbers all have 5 digits so I've tried 1* and 1????.
gt; gt; gt; =SUMIF(A1:A307,quot;1*quot;,F1:F307)
gt; gt; gt; =SUMIF(A1:A307,quot;1????quot;,F1:F307)
gt; gt; gt; don't work. Will someone please tell me what I'm doing incorrectly? Thanks
gt; gt; gt; in advance.