Using sumproduct in a dynamic range?

H

HammerJoe

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.
 
R

RichardSchollar

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
 
H

HammerJoe

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.
 
D

David Biddulph

-- converts a boolean true or false to 1 or 0, which you'd need if the terms
in SUMPRODUCT are separated by commas, but the multiplication will probably
do the job without the double unary minus.
Is it a surprise to you that you get a zero if your formula includes
....*(--(TRIM(BH3:BH18)="Apple")))*(--(TRIM(BH3:BH18)="Orange")))) ?
You have asked a row where BH = "Apple" AND BH = "Orange". It can't be
equal to both. You need an OR condition, not AND.
--
David Biddulph

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.
 
H

HammerJoe

Also maybe you can help me with this one.

I read about setting a dynamic range name to automatically expand the
list. I thought that would be a good solutin to my expanding list.
I named it CallDate and used the formula : =OFFSET(Tracker!$BD
$3,0,0,COUNT(Tracker!$BD:$BD),1)

BD being the column that has the dates that I want to verify against
todays month (and year).

=CountA(Calldate) does give me the right number of rows which means
that the Name is working, but replacing the ranges in the Sumproduct
with CallDate I get a #REF error.
Also tried using Indirect(CallDate) as well and get another error.
This formula makes me want to punch something... brb. :)
 
B

Bob Phillips

=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)



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.
 
B

Bob Phillips

You can't replace all ranges with a dynamic range, you have to have a
dynamic range for all different columns tested, but based upon the same
COUNT.

--
---
HTH

Bob


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

David Biddulph

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?
 
B

Bob Phillips

I think you are right David, it is overkill in this instance.

--
---
HTH

Bob


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

T. Valko

Create the dynamic ranges. You need 2 of them (but could do it another way
using 1).

This assumes your range in column BD will be contiguous (no empty/blank
cells *within* the range).

Insert>Name>Define
Name: Dates
Refers to: =OFFSET(BD$3,,,COUNTA(BD$3:BD$65536))

Name: Fruit
Refers to: =OFFSET(BH$3,,,COUNTA(BD$3:BD$65536))

Notice how *both* ranges are "keyed" on column BD using
COUNTA(BD$3:BD$65536)

Then your formula:

=SUMPRODUCT((TEXT(Dates,"m/yyyy")=TEXT(NOW(),"m/yyyy"))*(TRIM(Fruit)={"apple","orange"}))
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Range headings 0
SUMPRODUCT with date range running slow 1
Creating a Search using a combo box and then creating a report. 4
Dynamic Naming of Form Button 3
Need Help with a VBA subroutine 0
SUMPRODUCT column criteria 3
Development combinations with exclusion of columns 0
Help with Sumproduct searching cell contents 2

Top