Wildcard with sumif

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

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.
 
terrific!! It worked great!
What is the "--" ? I had tried that formula without -- and it didn't work.
Thank you SO much
 
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.
 
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).
 
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



message
 
thank you very much

Chip Pearson said:
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



message
Click to expand...
 
thank you!

Sloth said:
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).
Click to expand...
 
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.
 
=SUM(IF(($C$3:$C$26=G3)*(left($E$3:$E$26,8)="Complete"),$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 here:
http://www.xldynamic.com/source/xld.SUMPRODUCT.html

And J.E. McGimpsey has some notes at:
http://mcgimpsey.com/excel/formulae/doubleneg.html
 
Awesome, thanks Dave!

Dave Peterson said:
=SUM(IF(($C$3:$C$26=G3)*(left($E$3:$E$26,8)="Complete"),$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 here:
http://www.xldynamic.com/source/xld.SUMPRODUCT.html

And J.E. McGimpsey has some notes at:
http://mcgimpsey.com/excel/formulae/doubleneg.html
Click to expand...
 
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?
 
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.
 
Back
Top