Re: SUMPRODUCT is making me mad!

Tech-Archive recommends: Repair Windows Errors & Optimize Windows Performance

From: Bernard Liengme (bliengme_at_stfx.TRUENORTH.ca)
Date: 05/19/04 

Date: Wed, 19 May 2004 11:16:52 -0300

Hi Frank,
The double negation is not needed here since the addition operator coerces
Boolean to numeric
Best wishes
Bernard

"Frank Kabel" <frank.kabel@freenet.de> wrote in message
news:eBBbamaPEHA.680@TK2MSFTNGP11.phx.gbl...
> Hi
> several things are wrong :-)
> - SUMPRODUCT does not accept ranges like A:A. You have to use something
> like A1:A1000
> - SUMPRODUCT does not support wildcards (like SUMIF)
>
> One solution:
> =SUMPRODUCT(--(ISNUMBER(FIND("200312",A1:A1000))+ISNUMBER(FIND("200401"
> ,A1:A1000))+ISNUMBER(FIND("200403",A1:A1000))>0),B1:B1000)
>
>
>
>
> --
> Regards
> Frank Kabel
> Frankfurt, Germany
>
>
> > Hello all excel experts
> >
> > My problem:
> > I have a list with two columns A and B and I want to sum column B
> > based on filtred multiple criterias in column A. An example on the
> > list: Column A Column B
> > test200302abc 300
> > test200311dsf 400
> > test200312dsj 500
> > test200312adf 400
> > test200401xyz 100
> > test200402fsf 100
> > test200403sdf 300
> > test200403fsf 200
> > test200401fsf 100
> >
> > Now I want to sum all values that meet the criteria *200312* or
> > *200401* or *200403*, which sums up in this example to 1600.
> > My easy solution is to have a lot of sumif formulas but when I have
> up
> > to 24 criterias it's too much I think.
> > So I hoped that my other solution would be like this:
> >
> SUMPRODUCT((A:A="*"&"200312"&"*")*(A:A="*"&"200401"&"*")*(A:A="*"&"2004
> 03"&"*")*(B:B))
> > but it returns just #NUM!
> >
> > What´s wrong? Any suggestions on a alternative solution?
> >
> > All the best to THE ONE that solve my problem.
> >
> >
> > ---
> > Message posted from http://www.ExcelForum.com/
>

Relevant Pages

  • Re: Splitting/Combing data question
    ... settings. ... > Regards and thanks. ... > "Frank Kabel" wrote in message ... >>> Checked by AVG anti-virus system. ...
    (microsoft.public.excel.programming)
  • Re: General Question On Handling of Function results.t
    ... might confuse another programmer working new on the project, ... > Best Regards ... >> use exception handling if you do not see anny other way, ... but a simple boolean is not enough. ...
    (microsoft.public.dotnet.languages.vb)
  • Re: Delphi type events in C#
    ... Beste Grüsse / Best regards / Votre bien devoue ... procedure TMyNewEvent (Sender: TObject; MyStream: TStream; var MyFlag: boolean) Of Object; ... procedure DoMyEvent(Sender: TObject; AStream: TStream; var AFlag: boolean); ...
    (microsoft.public.dotnet.languages.csharp)
  • Re: Count repeats
    ... "Frank Kabel" wrote: ... > Daniel Bonallack wrote: ... >> With regard to the cell range change, I would just extend the range ...
    (microsoft.public.excel.worksheet.functions)
  • Re: SUMIF(AND) FUNCTION
    ... Had a separate discussion via private mail about SUMPRODUCT (but also ... > "Frank Kabel" wrote in message ... >> Frankfurt, Germany ...
    (microsoft.public.excel.worksheet.functions)