Sumproduct

[james.halliwell]

Hi everyone,

Please could someone help me out with a SUMPRODUCT question?

The first tab I have a summary sheet which I want to auto populate with a sumproduct function, the second tab is where the dates and values are I wish to sum the value if it’s returned in January

I had a go to try sum the entire range see below but it’s not working

=SUMPRODUCT(--('Alex Guest - Scotland'!$G$19:$G$1048576>=1/1/2009),--('Alex Guest - Scotland'!$G$19:$G$1048576<=3/1/2009),--('Alex Guest - Scotland'!$H$19:$H$1048576))

The ultimate goal I am trying to achieve is if there is a date in January in the date returned column and the php column does not contain a value I want it to sum in the summary sheet in January

Hopefully if anyone can advise that would be great then I can look at the formula and try workout to sum only if there is a value in the php column in January

Thanks in advance

[NBVC]

Those are pretty large ranges and may affect performance...

... but when using dates hard-coded in the Sumproduct() function, you need to enclose in double quotes and add +0

e.g

=SUMPRODUCT(--('Alex Guest - Scotland'!$G$19:$G$1048576>="1/1/2009"+0),--('Alex Guest - Scotland'!$G$19:$G$1048576<="31/1/2009"+0),--('Alex Guest - Scotland'!$H$19:$H$1048576))

Since you are using 2007, you can also use SUMIFS()

as in:

=SUMIFS('Alex Guest - Scotland'!$H$19:$H$1048576,'Alex Guest - Scotland'!$G$19:$G$1048576,">=1/1/2009",'Alex Guest - Scotland'!$G$19:$G$1048576,"<=31/1/2009")

noted that you indicated <=3/1/2009 should that be 31/1/2009 (i changed my formulas to that)

If you shorten the range drastically to you can even shorten formula to:

=SUMPRODUCT(--(TEXT('Alex Guest - Scotland'!$G$19:$G$1048576,"mmm")=TEXT(C$3,"mmm")),('Alex Guest - Scotland'!$H$19:$H$1048576))

and simply copy over to other cells in the row... my formula shows whole range.. but that is very slow.. you should shorten it.

[james.halliwell]

hi there,

First off many thanks on your advise works a treat the sumifs seems to work faster and use a little less memory

have rep'd for your fast and accurate response

could i be checky and ask your advise on one more issue if the PHP column on the work sheet contains a numerical value i would like it not to inculde that value in the sumifs formula

i need to create a differnt one to count just if its in january and and its a php value

many thanks

[james.halliwell]

apologies

had a bit of a blond moment there all i had to do is change the final arry to the php column

from

=SUMPRODUCT(--('Alex Guest - Scotland'!$G$19:$G$1048576>="1/1/2009"+0),--('Alex Guest - Scotland'!$G$19:$G$1048576<="30/1/2009"+0),--('Alex Guest - Scotland'!$H$19:$H$1048576))

to

=SUMPRODUCT(--('Alex Guest - Scotland'!$G$19:$G$1048576>="1/1/2009"+0),--('Alex Guest - Scotland'!$G$19:$G$1048576<="30/1/2009"+0),--('Alex Guest - Scotland'!$E$19:$E$1048576))

once again many thanks will make as solved