Wildcard with sumif
 

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,"1*",F1:F307)
=SUMIF(A1:A307,"1????",F1:F307)
don't work. Will someone please tell me what I'm doing incorrectly? Thanks
in advance.

Wildcard with sumif
 

Hi

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

--
Regards

Roger Govier


"fractallinda" wrote in message
...
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,"1*",F1:F307)
=SUMIF(A1:A307,"1????",F1:F307)
don't work. Will someone please tell me what I'm doing incorrectly?
Thanks
in advance.

Wildcard with sumif
 

terrific!! It worked great!
What is the "--" ? I had tried that formula without -- and it didn't work.
Thank you SO much

"fractallinda" wrote:

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,"1*",F1:F307)
=SUMIF(A1:A307,"1????",F1:F307)
don't work. Will someone please tell me what I'm doing incorrectly? Thanks
in advance.

Wildcard with sumif
 

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 Govier


"fractallinda" wrote in message
...
terrific!! It worked great!
What is the "--" ? I had tried that formula without -- and it didn't
work.
Thank you SO much

"fractallinda" wrote:

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,"1*",F1:F307)
=SUMIF(A1:A307,"1????",F1:F307)
don't work. Will someone please tell me what I'm doing incorrectly?
Thanks
in advance.

Wildcard with sumif
 

the "--" 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).

"fractallinda" wrote:

terrific!! It worked great!
What is the "--" ? I had tried that formula without -- and it didn't work.
Thank you SO much

"fractallinda" wrote:

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,"1*",F1:F307)
=SUMIF(A1:A307,"1????",F1:F307)
don't work. Will someone please tell me what I'm doing incorrectly? Thanks
in advance.

Wildcard with sumif
 

What is the "--" ?

The comparison (LEFT(A1:A307)="1") 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



"fractallinda" wrote in
message
...
terrific!! It worked great!
What is the "--" ? I had tried that formula without -- and it
didn't work.
Thank you SO much

"fractallinda" wrote:

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,"1*",F1:F307)
=SUMIF(A1:A307,"1????",F1:F307)
don't work. Will someone please tell me what I'm doing
incorrectly? Thanks
in advance.

Wildcard with sumif
 

many thanks!

"Roger Govier" wrote:

Hi

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

--
Regards

Roger Govier


"fractallinda" wrote in message
...
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,"1*",F1:F307)
=SUMIF(A1:A307,"1????",F1:F307)
don't work. Will someone please tell me what I'm doing incorrectly?
Thanks
in advance.

Wildcard with sumif
 

thank you very much

"Chip Pearson" wrote:

What is the "--" ?

The comparison (LEFT(A1:A307)="1") 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



"fractallinda" wrote in
message
...
terrific!! It worked great!
What is the "--" ? I had tried that formula without -- and it
didn't work.
Thank you SO much

"fractallinda" wrote:

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,"1*",F1:F307)
=SUMIF(A1:A307,"1????",F1:F307)
don't work. Will someone please tell me what I'm doing
incorrectly? Thanks
in advance.

Wildcard with sumif
 

thank you!

"Sloth" wrote:

the "--" 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).

"fractallinda" wrote:

terrific!! It worked great!
What is the "--" ? I had tried that formula without -- and it didn't work.
Thank you SO much

"fractallinda" wrote:

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,"1*",F1:F307)
=SUMIF(A1:A307,"1????",F1:F307)
don't work. Will someone please tell me what I'm doing incorrectly? Thanks
in advance.

Wildcard with sumif
 

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
"Complete" entered into the deadline column and some of the complete tasks
also include the date the task was completed in this column e.g. "Complete
02/2009".

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 "Complete" in column E:

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

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

{=SUM(IF(($C$3:$C$26=G3)*($E$3:$E$26="Complete*"), $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="Complete*"),$D$3: $D$26)))}

but this also returns 0 for all names.

What am I doing wrong!

Thanks,
Jim.

"Sloth" wrote:

the "--" 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).

"fractallinda" wrote:

terrific!! It worked great!
What is the "--" ? I had tried that formula without -- and it didn't work.
Thank you SO much

"fractallinda" wrote:

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,"1*",F1:F307)
=SUMIF(A1:A307,"1????",F1:F307)
don't work. Will someone please tell me what I'm doing incorrectly? Thanks
in advance.

Wildcard with sumif
 

=SUM(IF(($C$3:$C$26=G3)*(left($E$3:$E$26,8)="Compl ete"),$D$3:$D$26))
(still array entered)

You could also:
=SUMPRODUCT(--($C$3:$C$26=G3),--(LEFT($E$3:$E$26,8)="Complete"),$D$3:$D$26)
and enter it as a normal formula.

If you were looking for Complete anywhere in E3:E26, you could use:

=SUMPRODUCT(--($C$3:$C$26=G3),
--(ISNUMBER(SEARCH("complete",$E$3:$E$26))),
$D$3:$D$26)

Adjust the ranges to match--but you can't use whole columns (except in xl2007).

=sumproduct() likes to work with numbers. The -- stuff changes trues and falses
to 1's and 0's.

Bob Phillips explains =sumproduct() in much more detail he
http://www.xldynamic.com/source/xld.SUMPRODUCT.html

And J.E. McGimpsey has some notes at:
http://mcgimpsey.com/excel/formulae/doubleneg.html


Jim@Tech wrote:

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
"Complete" entered into the deadline column and some of the complete tasks
also include the date the task was completed in this column e.g. "Complete
02/2009".

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 "Complete" in column E:

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

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

{=SUM(IF(($C$3:$C$26=G3)*($E$3:$E$26="Complete*"), $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="Complete*"),$D$3: $D$26)))}

but this also returns 0 for all names.

What am I doing wrong!

Thanks,
Jim.

"Sloth" wrote:

the "--" 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).

"fractallinda" wrote:

terrific!! It worked great!
What is the "--" ? I had tried that formula without -- and it didn't work.
Thank you SO much

"fractallinda" wrote:

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,"1*",F1:F307)
=SUMIF(A1:A307,"1????",F1:F307)
don't work. Will someone please tell me what I'm doing incorrectly? Thanks
in advance.

--

Dave Peterson

Wildcard with sumif
 

Awesome, thanks Dave!

"Dave Peterson" wrote:

=SUM(IF(($C$3:$C$26=G3)*(left($E$3:$E$26,8)="Compl ete"),$D$3:$D$26))
(still array entered)

You could also:
=SUMPRODUCT(--($C$3:$C$26=G3),--(LEFT($E$3:$E$26,8)="Complete"),$D$3:$D$26)
and enter it as a normal formula.

If you were looking for Complete anywhere in E3:E26, you could use:

=SUMPRODUCT(--($C$3:$C$26=G3),
--(ISNUMBER(SEARCH("complete",$E$3:$E$26))),
$D$3:$D$26)

Adjust the ranges to match--but you can't use whole columns (except in xl2007).

=sumproduct() likes to work with numbers. The -- stuff changes trues and falses
to 1's and 0's.

Bob Phillips explains =sumproduct() in much more detail he
http://www.xldynamic.com/source/xld.SUMPRODUCT.html

And J.E. McGimpsey has some notes at:
http://mcgimpsey.com/excel/formulae/doubleneg.html


Jim@Tech wrote:

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
"Complete" entered into the deadline column and some of the complete tasks
also include the date the task was completed in this column e.g. "Complete
02/2009".

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 "Complete" in column E:

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

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

{=SUM(IF(($C$3:$C$26=G3)*($E$3:$E$26="Complete*"), $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="Complete*"),$D$3: $D$26)))}

but this also returns 0 for all names.

What am I doing wrong!

Thanks,
Jim.

"Sloth" wrote:

the "--" 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).

"fractallinda" wrote:

terrific!! It worked great!
What is the "--" ? I had tried that formula without -- and it didn't work.
Thank you SO much

"fractallinda" wrote:

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,"1*",F1:F307)
=SUMIF(A1:A307,"1????",F1:F307)
don't work. Will someone please tell me what I'm doing incorrectly? Thanks
in advance.

--

Dave Peterson

Wildcard with sumif
 

I've hit another snag with this data set ... Having added together all the
completed effort I now need to sum the effort corresponding to tasks in Jan &
Feb by name, but because some of the entries in the deadline column are text
(e.g. "Complete 02/2009") rather than dates, my formula returns #Value!

SUMPRODUCT(--($C$3:$C$26=G2),--(MONTH($E$3:$E$26)=1),$D$3:$D$26))

I have tried adding an ISERROR term to get around this:

=IF(ISERROR(SUMPRODUCT(--($C$3:$C$26=G2),--(MONTH($E$3:$E$26)=1),$D$3:$D$26)),0,SUMPRODUCT(--($C$3:$C$26=G2),--(MONTH($E$3:$E$26)=1),$D$3:$D$26))

But obviously the If(ISERROR arguement is always true so I always get 0.

How can I tell the Month((...) term to ignore cells that don't conform to
its format requirements?

"Dave Peterson" wrote:

=SUM(IF(($C$3:$C$26=G3)*(left($E$3:$E$26,8)="Compl ete"),$D$3:$D$26))
(still array entered)

You could also:
=SUMPRODUCT(--($C$3:$C$26=G3),--(LEFT($E$3:$E$26,8)="Complete"),$D$3:$D$26)
and enter it as a normal formula.

If you were looking for Complete anywhere in E3:E26, you could use:

=SUMPRODUCT(--($C$3:$C$26=G3),
--(ISNUMBER(SEARCH("complete",$E$3:$E$26))),
$D$3:$D$26)

Adjust the ranges to match--but you can't use whole columns (except in xl2007).

=sumproduct() likes to work with numbers. The -- stuff changes trues and falses
to 1's and 0's.

Bob Phillips explains =sumproduct() in much more detail he
http://www.xldynamic.com/source/xld.SUMPRODUCT.html

And J.E. McGimpsey has some notes at:
http://mcgimpsey.com/excel/formulae/doubleneg.html


Jim@Tech wrote:

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
"Complete" entered into the deadline column and some of the complete tasks
also include the date the task was completed in this column e.g. "Complete
02/2009".

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 "Complete" in column E:

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

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

{=SUM(IF(($C$3:$C$26=G3)*($E$3:$E$26="Complete*"), $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="Complete*"),$D$3: $D$26)))}

but this also returns 0 for all names.

What am I doing wrong!

Thanks,
Jim.

"Sloth" wrote:

the "--" 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).

"fractallinda" wrote:

terrific!! It worked great!
What is the "--" ? I had tried that formula without -- and it didn't work.
Thank you SO much

"fractallinda" wrote:

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,"1*",F1:F307)
=SUMIF(A1:A307,"1????",F1:F307)
don't work. Will someone please tell me what I'm doing incorrectly? Thanks
in advance.

--

Dave Peterson

Wildcard with sumif
 

Personally, I would fix that data (maybe two fields???). One for the text
(complete) and one for the date--and I'd use a real date.

Maybe even use data|text to columns to separate them and something else to
convert 02/2009 to a real date.

Life would be much easier then.

But you could use an array formula (yep, back to ctrl-shift-enter):

=SUM(IF(($C$3:$C$7=G3)
*(ISNUMBER($E$3:$E$7))*(MONTH(IF(ISNUMBER($E$3:$E$ 7),$E$3:$E$7))=1),
$D$3:$D$7))
(ctrl-shift-enter)

But that's gonna miss those messed up entries.



Jim@Tech wrote:

I've hit another snag with this data set ... Having added together all the
completed effort I now need to sum the effort corresponding to tasks in Jan &
Feb by name, but because some of the entries in the deadline column are text
(e.g. "Complete 02/2009") rather than dates, my formula returns #Value!

SUMPRODUCT(--($C$3:$C$26=G2),--(MONTH($E$3:$E$26)=1),$D$3:$D$26))

I have tried adding an ISERROR term to get around this:

=IF(ISERROR(SUMPRODUCT(--($C$3:$C$26=G2),--(MONTH($E$3:$E$26)=1),$D$3:$D$26)),0,SUMPRODUCT(--($C$3:$C$26=G2),--(MONTH($E$3:$E$26)=1),$D$3:$D$26))

But obviously the If(ISERROR arguement is always true so I always get 0.

How can I tell the Month((...) term to ignore cells that don't conform to
its format requirements?

"Dave Peterson" wrote:

=SUM(IF(($C$3:$C$26=G3)*(left($E$3:$E$26,8)="Compl ete"),$D$3:$D$26))
(still array entered)

You could also:
=SUMPRODUCT(--($C$3:$C$26=G3),--(LEFT($E$3:$E$26,8)="Complete"),$D$3:$D$26)
and enter it as a normal formula.

If you were looking for Complete anywhere in E3:E26, you could use:

=SUMPRODUCT(--($C$3:$C$26=G3),
--(ISNUMBER(SEARCH("complete",$E$3:$E$26))),
$D$3:$D$26)

Adjust the ranges to match--but you can't use whole columns (except in xl2007).

=sumproduct() likes to work with numbers. The -- stuff changes trues and falses
to 1's and 0's.

Bob Phillips explains =sumproduct() in much more detail he
http://www.xldynamic.com/source/xld.SUMPRODUCT.html

And J.E. McGimpsey has some notes at:
http://mcgimpsey.com/excel/formulae/doubleneg.html


Jim@Tech wrote:

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
"Complete" entered into the deadline column and some of the complete tasks
also include the date the task was completed in this column e.g. "Complete
02/2009".

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 "Complete" in column E:

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

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

{=SUM(IF(($C$3:$C$26=G3)*($E$3:$E$26="Complete*"), $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="Complete*"),$D$3: $D$26)))}

but this also returns 0 for all names.

What am I doing wrong!

Thanks,
Jim.

"Sloth" wrote:

the "--" 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).

"fractallinda" wrote:

terrific!! It worked great!
What is the "--" ? I had tried that formula without -- and it didn't work.
Thank you SO much

"fractallinda" wrote:

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,"1*",F1:F307)
=SUMIF(A1:A307,"1????",F1:F307)
don't work. Will someone please tell me what I'm doing incorrectly? Thanks
in advance.

--

Dave Peterson


--

Dave Peterson