# SumProduct Help

#### tanny81

##### Board Regular
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?

### Excel Facts

Formula for Yesterday
Name Manager, New Name. Yesterday =TODAY()-1. OK. Then, use =YESTERDAY in any cell. Tomorrow could be =TODAY()+1.

#### jonesy241

##### Board Regular
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...

##### MrExcel MVP
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

##### Board Regular
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

##### Board Regular
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?

##### MrExcel MVP
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

##### Board Regular
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

##### Board Regular
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,

##### MrExcel MVP
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

##### Board Regular
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,

Replies
7
Views
1K
Replies
3
Views
174
Replies
13
Views
2K
Replies
8
Views
896
Replies
5
Views
332

1,190,703
Messages
5,982,390
Members
439,777
Latest member
daleEH

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back