Re: Using sumproduct in a dynamic range?

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

As a matter of interest, do you need the SIGN function, Bob?
I could see the need for that if the terms that you are trying to use in an
OR function might both be true, but as in this case they are mutually
exclusive I would have thought one could live without it?
--
David Biddulph

"Bob Phillips" <bob.NGs@xxxxxxxxxxxxx> wrote in message
news:uVtJG$ITIHA.4880@xxxxxxxxxxxxxxxxxxxxxxx
=SUMPRODUCT(--(MONTH(BD3:BD18)=MONTH(TODAY())),--(YEAR(BD3:BD18)=YEAR(TODAY())),--(BD3:BD18<>""),SIGN((TRIM(BH3:BH18)="Apple")+(TRIM(BH3:BH18)="Orange")))

You don't need * and --, see
http://www.xldynamic.com/source/xld.SUMPRODUCT.html for a detailed
explanation.

--
---
HTH

Bob


(there's no email, no snail mail, but somewhere should be gmail in my
addy)



<HammerJoe@xxxxxxxxx> wrote in message
news:7bb99a14-64d2-41de-b99e-60ed7f38f3fe@xxxxxxxxxxxxxxxxxxxxxxxxxxxxxx
You just had to complicate things didnt you? :)

Of course I have to take the year in account as well <sigh>, I
overlooked that detail.
Your formula works, thanks for it.

What does '--' do in a formula?

I tried to expand the formula with more matches but getting errors
again.
This formula is such a pain to work with.

I am using your formula as :
=SUMPRODUCT(--(MONTH(BD3:BD18)=MONTH(TODAY()))*--(BD3:BD18<>"")*(--
(YEAR(BD3:BD18)=YEAR(TODAY()))*(--(TRIM(BH3:BH18)="Apple")))) which
works fine.

But I also want to include another fruit to the equation.
I tried adding *(--(TRIM(BH3:BH18)="Orange")))) to the end and it
doesnt work.

Get zero as a result.
Tried with commas variation and get errors either Value# or NA#.
So what is the trick here for the Sumproduct formula to add "Apples"
and "Oranges" matching "todays Month"?

Thanks for your help.


On Jan 1, 5:00 am, RichardSchollar <RichardSchol...@xxxxxxxxxxxxxx>
wrote:
Hi

Try:

=SUMPRODUCT(--(MONTH(A1:A18=MONTH(TODAY())),--(A1:A18<>""),--(B1:B18))

The --(A1:A18<>"") term is inserted as blank cells return a 1 inside
the MONTH function - which may or may not have caused you problems.
The above does assume that your dates don't span 2 or more years, as
then you may end up adding up values from the same month but different
years.

Richard

On Jan 1, 8:24 am, "Hammer...@xxxxxxxxx" <Hammer...@xxxxxxxxx> wrote:



Hi,

I am trying to use Sumproduct to give me the sum of a range but I cant
get it to work

Row A contains dates
Row B contains Boolean (true or false)

What I want to do is to sum all the Row B that are true and that row A
date is equal to current month date.

I've been playing with :
=SUMPRODUCT(A1:A18")=MONTH(TODAY())*(B1:B18=True) but it gives me an
error.
Tried different approaches and cant get it to work.

Can someone fix this for me please?
The second part of my question is how to adapt the formula to an
increase range.
Every day the row count increases by one, can the formula
automatically use all the rows that have dates instead of a fixed
range A1:A18?

If it is simpler to use VB it would be okay as well, but you will have
to tell me how to call the function from within the spreadsheet?

Thanks for the help.




.

Relevant Pages

  • Re: When The Deal Goes Down
    ... I didn't make a criticism I just said that I was uneasy about it. ... It must have been the MTV execs who said, "Bob, don't dare do anything ... In the end it doesn't matter. ... Some contexts matter to some of the people some of the time. ...
    (rec.music.dylan)
  • Re: Moya the little B!tch, pulls out of Wimbledon...
    ... bob wrote: ... Something tells me Carlos's fame has not come from Wimbledon ... believes it doesn't matter to you. ...
    (rec.sport.tennis)
  • Re: Sweet guitar
    ... Gibson or anyone else didnt make him one also. ... You know Bob. ... absolutely nothing special about those old PAF pickups. ...
    (alt.guitar)
  • Re: DeadRats justification for encouraging Robert Kolker
    ... reasons why I don't think Bob Kolker's lunatic posts deserve the ... you say Bob is dangerous; I say that a Jewish advocate ... whichever obtains is independent of my religious affiliation, politics, ... these things matter even less. ...
    (talk.origins)
  • Re: Can sing/cant sing - it wont stop me from going
    ... He's the top of the pile (or my pile, ... and I'm sure this is why Bob tolerates ... If it's just a matter of taste, your so called critical faculties are ...
    (rec.music.dylan)