SumProduct Help

[tanny81]

Hi All,

Im trying to perform the below, however the formula is returning nil values.

=SUMPRODUCT(--('Sheet2'!C2:C1000=B8),--('Sheet2'!G2:G1000>B4),--('Sheet2'!G2:G1000<D4))

To explain what this is attempting to do,
B8 contains a colour, for example the word red.
B4 & D4 contain dates.

The formula should basically count the number of times the word red appears in column C, sheet 2, but only when the date in column G, sheet2 is greater than or equal to B4 & less than or equal to D4

Is it possible to do this, and if so what amendments am i required to make?

 

[jonesy241]

I think you probably just didn't copy your formula completely and it should end with '=D4))'

Also, if you mean to say greater than or equal >= instead of just >.

If there are occurences between those two and that change doesn't solve your problem I would check the column with dates and make sure that is an actual date as Excel understands dates. Your sumproduct formula should work for what you're trying to do though...

 

[Aladin Akyurek]

Hi All,

Im trying to perform the below, however the formula is returning nil values.

=SUMPRODUCT(--('Sheet2'!C2:C1000=B8),--('Sheet2'!G2:G1000 > B4),--('Sheet2'!G2:G1000 < D4))

To explain what this is attempting to do,
B8 contains a colour, for example the word red.
B4 & D4 contain dates.

The formula should basically count the number of times the word red appears in column C, sheet 2, but only when the date in column G, sheet2 is greater than or equal to B4 & less than or equal to D4

Is it possible to do this, and if so what amendments am i required to make?

The formula is OK. Make sure that the dates are true dates and the items in column C do not have any leading/trailing spaces.

 

[tanny81]

Hi, I think your right. Careless mistake, but the tip for specifying whether something is greater than or equal was much appreciated. I never knew how to do that.

Thanks very much

 

[tanny81]

Hi All,

I've hit another snag with this formula.

=SUMPRODUCT(--('Sheet2'!C2:C1000=B8),--('Sheet2'!G2:G1000>=B4),--('Sheet2'!G2:G1000<=D4),--('Sheet2'!I2:I1000=TRUE))

This now works exactly how i would like it two, however i need to narrow the results down further. Basically columns J & G contain dates, and i only want to count the results where the difference between the two dates is equals 2 or less.

Can this be incorporated into a sumproduct formula, or will something different be required?

 

[Aladin Akyurek]

Hi All,

I've hit another snag with this formula.

=SUMPRODUCT(--('Sheet2'!C2:C1000=B8),--('Sheet2'!G2:G1000>=B4),--('Sheet2'!G2:G1000<=D4),--('Sheet2'!I2:I1000=TRUE))

This now works exactly how i would like it two, however i need to narrow the results down further. Basically columns J & G contain dates, and i only want to count the results where the difference between the two dates is equals 2 or less.

Can this be incorporated into a sumproduct formula, or will something different be required?

Given the incomplete specs:

Control+shift+enter...

=SUM(IF(Sheet2!$C$2:$C$1000=B8,IF(Sheet2!$I$2:$I$1000,ABS(Sheet2!$G$2:$G$1000-Sheet2!$J$2:$J$1000)<=2)+0)))

 

[tanny81]

Hi apologies if there were not enough details, i thought i had covered most of it in the original post.

The current formula is:

=SUMPRODUCT(--('Sheet2'!C2:C1000=B8),--('Sheet2'!G2:G1000>=B4),--('Sheet2'!G2:G1000<=D4),--('Sheet2'!I2:I1000=TRUE))

Where

B8 = Red
B4 = 01/06/07
D4 = 01/07/07

This counts instances from the table in sheet 2 where C2:C1000 = Red, but only when the date in G2:G1000 is between 01/06/07 & 01/07/07 and the value in I2:I1000 = TRUE.

I still need the formula to perform all of the above, but in addition, it should only count the instance the word red appears when the difference between the dates in columns J & G equals 2 or less.

Thanks,

 

[tanny81]

Is anybody able to help with this?? If i haven't explained myself very well apologies, just let me know which bit doesn't make sense and i will try to clear it up.

Thanks,

 

[Aladin Akyurek]

Hi apologies if there were not enough details, i thought i had covered most of it in the original post.

The current formula is:

=SUMPRODUCT(--('Sheet2'!C2:C1000=B8),--('Sheet2'!G2:G1000>=B4),--('Sheet2'!G2:G1000<=D4),--('Sheet2'!I2:I1000=TRUE))

Where

B8 = Red
B4 = 01/06/07
D4 = 01/07/07

This counts instances from the table in sheet 2 where C2:C1000 = Red, but only when the date in G2:G1000 is between 01/06/07 & 01/07/07 and the value in I2:I1000 = TRUE.

I still need the formula to perform all of the above, but in addition, it should only count the instance the word red appears when the difference between the dates in columns J & G equals 2 or less.

Thanks,

Control+shift+enter:

=SUM(IF(Sheet2!$I$2:$I$1000,IF(Sheet2!$C$2:$C$1000=B8,IF(Sheet2!$G$2:$G$1000-DAY(Sheet2!$G$2:$G$1000)+1=B4,IF(ABS(Sheet2!$G$2:$G$1000-Sheet2!$J$2:$J$1000)<=2)+0))))

 

[tanny81]

Hi,

I have tried the formula you posted, but when i go to enter it i just get an error message telling me that the formula contains an error. Do you know what might be causing this?

Thanks,