Sumproduct Help

It is certainly smirking! I'll get the b*%$*d, out come the matches.

"Frank Kabel" wrote in message
...
Bob Phillips wrote:
Indeed it was. My solution (or lack of it) is still in tatters on
the
floor.

Bob

and it's rotfl, isn't it vbg
Frank

Hi

=SUMPRODUCT((CategoryRange=1)*(MONTH(DateRange)=1) *(SumRange))
when the year isn't essential, or something like
=SUMPRODUCT((CategoryRange=1)*(MONTH(DateRange)=1) *(YEAR(DateRange)=2004)*(S
umRange))
for category1 and January. Replace CategoryRange, DateRange and SumRange in
formula with references to proper ranges in your table, or with named ranges
defined by you.


--
(When sending e-mail, use address )
Arvi Laanemets


"gilbert " wrote in message
...
Would highly appreciate if someone could give me some thoughts on how
should I go about using Sumproduct given that I have six categories of
item and each category, I have different dates for the items in each
category.

Eg :-

Category 1 (Jan) - Need sum total of items of category 1 that appeared
in the month of January

Category 2 (Feb) - Need sum total of items of category 2 that appeared
in the month of February

Category 3 (Mac)
Category 2
Category 3
Category 4
Category 5
Category 6

The dates of daily dates, that is dd/mm/yy.

Please help.


---
Message posted from http://www.ExcelForum.com/

Hi
in addition to Arvi's answer you may take a look at pivot tables to
create a report for your source data:
Have a look at
http://www.cpearson.com/excel/pivots.htm
http://peltiertech.com/Excel/Pivots/pivotstart.htm
http://www.contextures.com/xlPivot02.html
to get some starting information

--
Regards
Frank Kabel
Frankfurt, Germany

"gilbert " schrieb im
Newsbeitrag ...
Would highly appreciate if someone could give me some thoughts on how
should I go about using Sumproduct given that I have six categories
of
item and each category, I have different dates for the items in each
category.

Eg :-

Category 1 (Jan) - Need sum total of items of category 1 that
appeared
in the month of January

Category 2 (Feb) - Need sum total of items of category 2 that
appeared
in the month of February

Category 3 (Mac)
Category 2
Category 3
Category 4
Category 5
Category 6

The dates of daily dates, that is dd/mm/yy.

Please help.


---
Message posted from http://www.ExcelForum.com/

Hi Arvi,

I tried your recommendation but it won't work...any thoughts where did
I go wrong?

My input is : -

=SUMPRODUCT(('PO Ctrl'!G10:G749="Materials")*(MONTH('PO
Ctrl'!I10:I749)=1)*('PO Ctrl'!E10:E749))

Where column G is the category
Where column I is the date range
Where column E is the values

I need to sum the values of the category named "Materials" that fall
under the month of January. FYI, my date is formatted as dd/mm/yy. I
suppose there is no need for me to define the year, must I?

Please help to correct my inputs....


---
Message posted from http://www.ExcelForum.com/

Hi
normaly the formula should work. what error did you get? What does not
work?

--
Regards
Frank Kabel
Frankfurt, Germany

"gilbert " schrieb im
Newsbeitrag ...
Hi Arvi,

I tried your recommendation but it won't work...any thoughts where
did
I go wrong?

My input is : -

=SUMPRODUCT(('PO Ctrl'!G10:G749="Materials")*(MONTH('PO
Ctrl'!I10:I749)=1)*('PO Ctrl'!E10:E749))

Where column G is the category
Where column I is the date range
Where column E is the values

I need to sum the values of the category named "Materials" that fall
under the month of January. FYI, my date is formatted as dd/mm/yy. I
suppose there is no need for me to define the year, must I?

Please help to correct my inputs....


---
Message posted from http://www.ExcelForum.com/

Hi
try the following:
=SUMPRODUCT(('PO
Ctrl'!G10:G10000="Materials")*(MONTH('POCtrl'!I10: I10000)=1)*('POCtrl'!
I10:I10000"")*('PO Ctrl'!E10:E10000))

I only added a condition for checking that your date range is not empy
and made the ranges larger. Note: You can't use a range like E:E in
SUMPRODUCT

--
Regards
Frank Kabel
Frankfurt, Germany

"gilbert " schrieb im
Newsbeitrag ...
Hi Arvi/Frank,

Sorry to bother you...I finally get what I wanted....the earlier
problem arised due to I sumproduct the wrong range (which contained
no
data at all), that's why it showed me with "#Value". I keep on trying
with dummy data, I managed to get through with it.....thanks to your
help....BTW, can't we sumproduct the whole range whereby the range
will
be entered with data in the future? FYI, I have a worksheet
containing
about 740 rows.....currently only used up abt 200 rows, I need to use
sumproduct function to extract the data out....and I need to define
the
range exactly (ie. the 200 rows only), I can't define the range for
all
740 rows. Can it be done some other way?


---
Message posted from http://www.ExcelForum.com/

Hi
in the range E10E749 is there some text? You may try
=SUMPRODUCT(('POCtrl'!G10:G10000="Materials")*(MON TH('POCtrl'!I10:I1000
0)=1)*('POCtrl'!I10:I10000""),('PO Ctrl'!E10:E10000))

--
Regards
Frank Kabel
Frankfurt, Germany


Hi Frank,

I have tried the suggested formula but it seem like not working...it
will show "#value". I am sure I have keyed in exactly as what you
suggested.

=SUMPRODUCT(('PO Ctrl'!$G$10:$G$749=Summaries!$A6)*(MONTH('PO
Ctrl'!$I$10:$I$749)=D$3)*('PO Ctrl'!$I$10:$I$749"")*('PO
Ctrl'!$E$10:$E$749))

Where Summaries!A6 is the title, ie Material
Where Summaries!D3 is the month in number, ie 1, 2, 3, ....

Before adding your suggested condition, the formula is working....so,
I believe we need to fine tune on the condition part?

Please advise.

Thank you.

Rgds,
Gilbert


---
Message posted from http://www.ExcelForum.com/

Hi Frank,

It doesn't seem to work as well.....

=SUMPRODUCT(('PO Ctrl'!$G$10:$G$749=Summaries!$A6)*(MONTH('PO
Ctrl'!$I$10:$I$749)=D$3)*('PO Ctrl'!$I$10:$I$749""),('PO
Ctrl'!$E$10:$E$749))

I hope I did not erronously key in the formula, did I? There is no text
as far as column E is concern, they are all value (numbers) with some
cells empty (ie. no value). Could that have impact to the formula?

If we can't get it done this way, do we have alternative way to do that
where we will still get the same results?

Please advise.


---
Message posted from http://www.ExcelForum.com/

Hi Gilbert
could you email me your sheet as the formula looks o.k. for me. Wat
error did you get with this formula?

email: frank[dot]kabel[at]freenet[dot]de

--
Regards
Frank Kabel
Frankfurt, Germany


Hi Frank,

It doesn't seem to work as well.....

=SUMPRODUCT(('PO Ctrl'!$G$10:$G$749=Summaries!$A6)*(MONTH('PO
Ctrl'!$I$10:$I$749)=D$3)*('PO Ctrl'!$I$10:$I$749""),('PO
Ctrl'!$E$10:$E$749))

I hope I did not erronously key in the formula, did I? There is no
text as far as column E is concern, they are all value (numbers) with
some cells empty (ie. no value). Could that have impact to the
formula?

If we can't get it done this way, do we have alternative way to do
that where we will still get the same results?

Please advise.


---
Message posted from http://www.ExcelForum.com/

Hi Gilbert
I would assume that some value in your source is not in the format
SUMPRODUCT expected the value.
You may try the following email address:
frank[dot]kabel[at]mummert[dot]de

--
Regards
Frank Kabel
Frankfurt, Germany


Hi Frank,

I tried to email you but your email system.....but was return back
for
it was blocked for spam.

Basically, excel returned me with #value. So, I would suppose the
formula is correct then...just that the source database might due to
some problem causing this, am I right?

Thank you.

Rgds,
Gilbert


---
Message posted from http://www.ExcelForum.com/