SUMPRODUCT issues

[vicomte777]

Hi
I'm having some sumproduct issues after having tried various countifs. Hopefully you experts can help.
My apologies, I'm now on my linux computer at home and not the windows environment at work, so I will try and recreate the functions from memory, but may get them slightly wrong.

[TABLE="class: grid, width: 500, align: center"]
<tbody>[TR]
[TD][/TD]
[TD]A[/TD]
[TD]B[/TD]
[TD]C[/TD]
[TD]D[/TD]
[TD]E[/TD]
[TD]F
[/TD]
[TD]G
[/TD]
[/TR]
[TR]
[TD]1
[/TD]
[TD]Stock Date[/TD]
[TD]Sell Date[/TD]
[TD]Type[/TD]
[TD]Note[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]2
[/TD]
[TD]01/01/2015[/TD]
[TD][/TD]
[TD]A Blue[/TD]
[TD]Error Duplicate entry[/TD]
[TD][/TD]
[TD]Date
From[/TD]
[TD]01/01/2015[/TD]
[/TR]
[TR]
[TD]3
[/TD]
[TD]01/01/2015[/TD]
[TD]16/01/2015[/TD]
[TD]Blue x3[/TD]
[TD][/TD]
[TD][/TD]
[TD]14[/TD]
[TD]Blue[/TD]
[/TR]
[TR]
[TD]4
[/TD]
[TD]06/01/2015[/TD]
[TD]12/01/2015[/TD]
[TD]Yellow x9[/TD]
[TD][/TD]
[TD][/TD]
[TD]18[/TD]
[TD]Orange[/TD]
[/TR]
[TR]
[TD]5
[/TD]
[TD]10/02/2015[/TD]
[TD]18/02/2015[/TD]
[TD]3x Orange[/TD]
[TD][/TD]
[TD][/TD]
[TD]21[/TD]
[TD]Yellow[/TD]
[/TR]
[TR]
[TD]6
[/TD]
[TD]08/01/2015[/TD]
[TD]26/02/2015[/TD]
[TD]Blue x1[/TD]
[TD]Error entered wrong dates[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]7
[/TD]
[TD]02/02/2015[/TD]
[TD]01/03/2015[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]8
[/TD]
[TD]13/12/2014[/TD]
[TD][/TD]
[TD][/TD]
[TD]Error [/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]9
[/TD]
[TD]02/05/2014[/TD]
[TD]20/01/2015[/TD]
[TD]1 Orange[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]10[/TD]
[TD]06/02/2015[/TD]
[TD]21/02/2015[/TD]
[TD]3 Blue[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

Ok - So the aim here is to only count the rows where:
a) the date in column A is after 01/01/2015
b) the difference in dates between column B and A is less than 14 days for Blue, 18 days for Orange and 21 days for Yellow
- this period in days may change in the future so I've placed them in cells with an intention that the period can be easily updated
c) a count of each colour
d) discounting any rows which have the word Error in column D

Now, say you sell the products Blue, Yellow and Orange but you only want to give sales bonus's when the time from stock date to sell date is less than 14 days... and bonus's are based on orders, not quantity within the order.

The actual sheet has different parameters, and I'm in a totally different industry/environment however I thought the narrative might help (it does have the dates which is causing the issue), but generally the same format. The actual sheet is some 10,000 rows long.

Have attempted:
COUNTIF - For example tell me how many Blues:
COUNTIFS(A2:A15000,">="&$G$2,B2:B15000,"<="&A2:A15000+$F$3,C2:C15000,"*Blue*",D2:D15000,"<>"&"*Error*") - I can't use 'MONTHS()' within COUNTIF/COUNTIFS.

SUMPRODUCT - As I know COUNTIF/COUNTIFS do not like 'MONTH()' functions, as I may need to break data down into individual Months based on Sale dates. In any case I've tried:
So again for Blue - Something like this
SUMPRODUCT(--(A2:A15000>=$G$2),--(B2:B15000<>""),--((B2:B15000-A2:A15000)>=$F$3),--(ISNUMBER(SEARCH("Blue",C2:C15000))),--(NOT(D2:D15000<>"*Error*"))

The issue comes down to subtracting one column of dates from the other and comparing that result to the period-difference I've set. Particularly for the SUMPRODUCT function, I get #VALUE errors where there are empty cells in column B. Over a short range of data, Excel seems to be able to handle it, but when looking at 7000 rows of data, the SUMPRODUCT function dies and just display's '#VALUE'.

I'm not very good with SUMPRODUCT being new to it as of around 48 hours ago, however were I wanting to look at Blue sales in the month of Feb I know I'd have fare more luck looking at a SUMPRODUCT function like:
SUMPRODUCT(--(A2:A15000>=$G$2),--(B2:B15000<>""),--(MONTH(B2:B15000)=2),--((B2:B15000-A2:A15000)>=$F$3),--(ISNUMBER(SEARCH("Blue",C2:C15000))),--(NOT(ISNUMBER(SEARCH("Error",D2:D15000)))))

Also, the input variables like the sale periods and output results of these functions are on a different sheet to the data

Ok ... I think the above recreates the general gist - hopeing you can help as the formulas aim to automate a process I've only just discovered a colleague has been manually calculating based on Autofilters for years ... I may be inheriting the process and know there should be a way of calculating this automatically.

 

[vicomte777]

Hi, just confirming I have tried taking the date ranges and using the condition --(B2:B15000<>"") which attempts to remove empty/null values from the array. I've also tried multiplication of the output of the date-date (B-A columns) *1, and tried +0, and various other methods to attempt to negate the dreaded value errors in sumproduct results. Really somewhat stumpe. I've tried placing --(IF(ISBLANK(B2:B15000),0,B2:B15000-A2:A15000<=$F$3)) in there and #Value still comes out. It really seems to be getting confused with the date minus date is less than 'x' and wondered whether it's some issue with the recognition of the dates, so put Date(year( etc)) around the dates to force their recognition...still no luck. When I attempt to step through the output, I get {1,0,0,0,1,0,1,0,1,#Value,0,1,1,0.. Etc}

Anyway, really stumped. Wish there was a switch to sumproduct to treat null/empty cells as zeroes.

 

[Snakehips]

vicomte,


Maybe try....

Excel 2007
	A	B	C	D	E	F	G	H
1	Stock Date	Sell Date	Type	Note
2	01/01/2015		A Blue	Error Duplicate entry		Date
3						From	01/01/2015	COUNT
4	01/01/2015	16/01/2015	Blue x3			14	Blue	0
5	06/01/2015	12/01/2015	Yellow x9			18	Orange	2
6	10/02/2015	18/02/2015	3x Orange			21	Yellow	1
7	08/01/2015	26/02/2015	Blue x1	Error entered wrong dates
8	02/02/2015	01/03/2015
9	13/12/2014			Error
10	02/05/2014	20/01/2015	1 Orange
11	06/02/2015	21/02/2015	3 Blue
12	02/05/2015	12/05/2015	1 Orange	An error
13	02/05/2015	12/05/2015	2 Orange
Sheet4

Cell Formulas
Range	Formula
H4	=SUMPRODUCT((ISNUMBER(SEARCH($G4,C$2:C$20)))*(NOT(ISNUMBER(SEARCH("ERROR",UPPER(D$2:D$20)))))*(A$2:A$20>=$G$3)*(B$2:B$20-A$2:A$20<=$F4)*(B$2:B$20-A$2:A$20>0))

Hope that helps.

 

[vicomte777]

Hi Snakehips

Thank you, I used some of your formula and unfortunately didn't work in the active sheet, however with some tweaking it suddenly did.

What I worked out is essentially, while this works:

[COLOR=#ff0000]B$2:B$20-A$2:A$20<=$F4 [/COLOR]

Code like this doesn't:

[[COLOR=#ff0000]A$2:A$20>=[COLOR=#ff0000]B$2:B$20-[/COLOR]$F4[/COLOR]

i.e. the calculation needs to be done on the left side of the condition being presented, as soon as I put the calculation on the right, it just wouldn't work for me.

Anyway, just wanted to touch base and say it is working and I'm feeling more confident with SUMPRODUCT now (despite being faaar from an expert)