SumProduct Help

tanny81

Board Regular
Joined
Mar 2, 2006
Messages
175
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

Is there a shortcut key for strikethrough?
Ctrl+S is used for Save. Ctrl+5 is used for Strikethrough. Why Ctrl+5? When you use hashmarks to count |||| is 4, strike through to mean 5.
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...
 
Upvote 0
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.
 
Upvote 0
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
 
Upvote 0
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?
 
Upvote 0
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)))
 
Upvote 0
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,
 
Upvote 0
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,
 
Upvote 0
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))))
 
Upvote 0
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,
 
Upvote 0

Forum statistics

Threads
1,213,485
Messages
6,113,931
Members
448,533
Latest member
thietbibeboiwasaco

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.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

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

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

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
Back
Top