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.