Sumproduct Help

  • Thread starter Thread starter gilbert
  • Start date Start date
G

gilbert

Would highly appreciate if someone could give me some thoughts on ho
should I go about using Sumproduct given that I have six categories o
item and each category, I have different dates for the items in eac
category.

Eg :-

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

Category 2 (Feb) - Need sum total of items of category 2 that appeare
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
 
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.
 
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....
 
Hi Arvi/Frank,

Sorry to bother you...I finally get what I wanted....the earlie
problem arised due to I sumproduct the wrong range (which contained n
data at all), that's why it showed me with "#Value". I keep on tryin
with dummy data, I managed to get through with it.....thanks to you
help....BTW, can't we sumproduct the whole range whereby the range wil
be entered with data in the future? FYI, I have a worksheet containin
about 740 rows.....currently only used up abt 200 rows, I need to us
sumproduct function to extract the data out....and I need to define th
range exactly (ie. the 200 rows only), I can't define the range for al
740 rows. Can it be done some other way
 
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
 
Hi Frank,

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

=SUMPRODUCT(('PO Ctrl'!$G$10:$G$749=Summaries!$A6)*(MONTH('P
Ctrl'!$I$10:$I$749)=D$3)*('PO Ctrl'!$I$10:$I$749<>"")*('P
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,
believe we need to fine tune on the condition part?

Please advise.

Thank you.

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

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

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

I hope I did not erronously key in the formula, did I? There is no tex
as far as column E is concern, they are all value (numbers) with som
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 tha
where we will still get the same results?

Please advise
 
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
 
Hi Frank,

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

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

Thank you.

Rgds,
Gilber
 
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
 
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))
Click to expand...
...

If the formula above returns #VALUE!, then the *only* possible causes are

1. some of the cells referenced evaluate to #VALUE!;
2. some (or all) of the cells in 'PO Ctrl'!$I$10:$I$749 aren't date serial
numbers.

Check the first possibility by summing each range separately. If any of the sums
return #VALUE!, that's likely to be the cause. If none return error, then check
'PO Ctrl'!$I$10:$I$749 with the formulas

=COUNT('PO Ctrl'!$I$10:$I$749)=740

and

=SUMPRODUCT(MONTH('PO Ctrl'!$I$10:$I$749))

If the first returns FALSE or the second returns #VALUE!, you have a data
cleansing exercise. If the first returns TRUE and the second returns a number,
then you'll need to check each part of the original formula, namely,

=SUMPRODUCT(--('PO Ctrl'!$G$10:$G$749=Summaries!$A6))

=SUMPRODUCT(--(MONTH('PO Ctrl'!$I$10:$I$749)=D$3))

=SUMPRODUCT(--('PO Ctrl'!$I$10:$I$749<>""))

=SUM('PO Ctrl'!$E$10:$E$749)

If all of these return numbers, then you'll need to check your transition
formula evaluation setting. Tools > Options, Transition tab in the dialog.
 
Harlan said:
...
..

If the formula above returns #VALUE!, then the *only* possible causes
are
Click to expand...

Hi Harlan

just as a follow up to explain this error. Gilbert sent me his
worksheet and the reasons for the non working formula were:
1. cell D$3 contains the month name as STRING (e.g. 'January') but the
range G10:G749 contains a date value (e.g. 01-01-2004). So this
couldn't work (of course this was not the reason for the #VALUE error).
So first thing was to change this to
TEXT('PO Ctrl'!$I$10:$I$749,"MMMM")=D$3

2. The range G10:G749 contains an IF formula which would return "" if
the condition is not met. Using the MONTH function on these "" results
caused the #VALUE error. Problem was also solved by the above formula
change

At Gilbert: I hope my changed formula is working for you?
 
Hi Frank,

Yes...now my worksheet is working perfectly....thanks for you
help...and for the benefit of the rest of the readers...here is th
complete formula suggested by Frank....

=SUMPRODUCT(('PO Ctrl'!$G$10:$G$350=Summaries!$A6)*(TEXT('P
Ctrl'!$I$10:$I$350,"MMMM")=D$4)*('PO Ctrl'!$I$10:$I$350<>""),('P
Ctrl'!$E$10:$E$350))

Once again, I would like to thank Frank for his great help.

Thank you..


Regards,
Gilber
 
...
2. The range G10:G749 contains an IF formula which would return "" if
the condition is not met. Using the MONTH function on these "" results
caused the #VALUE error. Problem was also solved by the above formula
change
Click to expand...
...

As long as no cell in either 'PO Ctrl'!$G$10:$G$749 or Summaries!$A6 evaluates
to an error value, none of the entries in

'PO Ctrl'!$G$10:$G$749=Summaries!$A6

should evaluate to error values. As far a I can see, 'PO Ctrl'!$G$10:$G$749
wasn't being passed through the MONTH function. Do you mean both cols G and P in
'PO Ctrl' needed to be wrapped inside TEXT(.,"MMMM")?
 
Harlan Grove said:
...
..

As long as no cell in either 'PO Ctrl'!$G$10:$G$749 or Summaries!$A6 evaluates
to an error value, none of the entries in

'PO Ctrl'!$G$10:$G$749=Summaries!$A6

should evaluate to error values. As far a I can see, 'PO Ctrl'!$G$10:$G$749
wasn't being passed through the MONTH function. Do you mean both cols G and P in
'PO Ctrl' needed to be wrapped inside TEXT(.,"MMMM")?
Click to expand...


Hi Harlan
sorry it was too late for me and I got the wrong column in my previous
post :-)
Change range G10:G749 in my explanantion to I10:I749

Frank
 
THANK YOU, THANK YOU, THANK YOU!!!!!!!!! I HAVE BEEN SCROLLING VARIOU
FORUMS TO TRY AND FIND OUT HOW TO USE THIS FORMULA. ALL MY QUESTION
HAVE BEEN ANSWERED AND MY SHEET IS WORKING JUST FINE.

THANKS FOR THE CLEAR, CONCISE, DIALOG HERE. AS A NOVICE EXCEL USER, I
WAS REALLY HELPFUL
 
Hi rmm30,

I am glad you have benefited from our discussion as well. Since you ar
so new to this forum, you can post any question and let the expert
teach you / show you / correct you...provided you have posted
relevant question on Excel.

On behalf of Excel Forum, I welcome you to the board.

Cheers... :)

Gilber
 
Back
Top