SUMPRODUCT Help Please.....

[Dermot]

I have read through the explantion of SUMPRODUCT here........
http://www.xldynamic.com/source/xld.SUMPRODUCT.html
I can understand the reasoning behind the tables 3 4 5 and 6......but when
it comes to actually using SUMPRODUCT for conditional calculations I just
can't get it to work.
How can I get it calculate say...........
Column A = list of Dates
Column B = List of companies.
Each row in relation to the dates and contains two cells with monetary
values i.e 200 Fee
Using SUMPRODUCT
How do I add the two cells monetary values for each company between and
calculate he complete total of thee between a period of two dates?
Any explanations or even a link to a downloadable example would be very
much appreciated, as I have spent a lot of time trying to figure out how to
reason this out.
Thanks

 

[Biff]

Hi!

What does this mean:

Each row in relation to the dates and contains
two cells with monetary values i.e 200 Fee

Biff

 

[ScottO]

I think I can interpret it (but I'm not *absolutely* sure) ...
If the dates are in ColA, and the Company names are in ColB, and Fee1
is in ColC, and Fee2 is in ColD, and a formula to add Fee1 + Fee2 is
in ColE,
and the data rows go from 2 to 100,
then a formula like
=SUMPRODUCT(--(StartDate<=A2:A100),--(EndDate>=A2:A100),--(CompanyNam
e=B2:B100),--(E2:E100)) should do it.
Note that StartDate, EndDate & CompanyName all indicate references to
cells that contain your search parameters.
Rgds,
ScottO


| Hi!
|
| What does this mean:
|
| >Each row in relation to the dates and contains
| >two cells with monetary values i.e 200 Fee
|
| Biff
|
| | >I have read through the explantion of SUMPRODUCT here........
| > http://www.xldynamic.com/source/xld.SUMPRODUCT.html
| > I can understand the reasoning behind the tables 3 4 5 and
6......but when
| > it comes to actually using SUMPRODUCT for conditional
calculations I just
| > can't get it to work.
| > How can I get it calculate say...........
| > Column A = list of Dates
| > Column B = List of companies.
| > Each row in relation to the dates and contains two cells with
monetary
| > values i.e 200 Fee
| > Using SUMPRODUCT
| > How do I add the two cells monetary values for each company
between and
| > calculate he complete total of thee between a period of two
dates?
| > Any explanations or even a link to a downloadable example would
be very
| > much appreciated, as I have spent a lot of time trying to figure
out how
| > to
| > reason this out.
| > Thanks
|
|

 

[Roger Govier]

Hi Dermot

I notice from previous postings you have made on this subject you
mention your dates as 23.12.05 for example.
If this is the case, then Sumproduct will not work as you expect because
these are not valid Excel date formats.
Change them to something like 23/12/05 and you should find it will work.
The easy way to convert your entries would be to use Data>Text to
Columns>Next>Next>choose Date DMY>Finish

 

[Dermot]

Hi Scotto,
Thanks for the reply.
Your understanding of my enquiry is correct except.......
To quote you:-
"and a formula to add Fee1 + Fee2 is in ColE"
The spreadsheet I am using doesn't have a Fee 1 + Fee2 column
Would I need to include one or can this calculation be included in the
formula?

 

[Bob Phillips]

Do it in the formula then

=SUMPRODUCT(--(StartDate<=A2:A100),--(EndDate>=A2:A100),
--(CompanyName=B2:B100),(C2:C100+D2100))

--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)

 

[Dermot]

Thanks Scotto

 

[Dermot]

Thanks Bob
I have eventually got it to work.....you did mention to me in a previous
posting to ensure I was using the correct date format........I thought I had
understood ........but when I red Rogers posting below.......Data | Text to
Columns etc......I wasn't although it appeared to be the correct format
visually.....it was text instead of a date format I was using......
Many thanks for your help with this problem for me.
Dermot
Sorry about the thoughtless earlier posting.

 

[Dermot]

Thanks Roger
I have struggled with this.
I had used the format 23/12/05.......but I assume it was text and not a date
format.
Following your explanation Date | Text to Column etc.....and selecting date
has resolved my problem.
I am not sure how I managed to do this....as the method I used
was.....selected ROW A | Format|Date|23/12/05...........should this have
resulted in correct Excel date format?

 

[Roger Govier]

Hi Dermot,
I'm glad you have now got it resolved.
Just for your information, with data in the column in the format
13.12.05, just selecting column A and Formatting as date DD/MM/YY format
will not do anything.
Formatting the column that way, then inputting a date value will cause
the date value to be shown in that format.
In your case, it did need to go through the conversion process first.

 

[Dermot]

Thanks again Roger
Best wishes
Dermot

Hi Dermot,
I'm glad you have now got it resolved.
Just for your information, with data in the column in the format
13.12.05, just selecting column A and Formatting as date DD/MM/YY format
will not do anything.
Formatting the column that way, then inputting a date value will cause
the date value to be shown in that format.
In your case, it did need to go through the conversion process first.