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" wrote in message
...
=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)
wrote in message
...
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
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, " 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.