Sumif or Sumproduct with 3 conditions or criteria

[Jbooze]

I have searched this forum and not found a formulat that will work. I need to check 3 conditions and if all three are true sum the values in a range of cells. One formula I have tried is:

=SUMPRODUCT((Configuration!A3:A370=B11)*(Configuration!C3:C370>=(T3)*(Configuration!C3:C370<=(U3)*(Configuration!D3:D370))))

Where B11 is the day of week (Monday, Tuesday, etc.), T3 is the starting date in the range and U3 is the end date in the range. If all 3 are true in columns A and the two conditions for C, then sum cells for True rows in Configuration!D3:D370

Any ideas?

 

[scottylad2]

I have searched this forum and not found a formulat that will work. I need to check 3 conditions and if all three are true sum the values in a range of cells. One formula I have tried is:

=SUMPRODUCT((Configuration!A3:A370=B11)*(Configuration!C3:C370>=(T3)*(Configuration!C3:C370<=(U3)*(Configuration!D3:D370))))

Where B11 is the day of week (Monday, Tuesday, etc.), T3 is the starting date in the range and U3 is the end date in the range. If all 3 are true in columns A and the two conditions for C, then sum cells for True rows in Configuration!D3:D370

Any ideas?

What do you have in A3:A370? are they days of the week or actual dates formatted as days,Monday, Tuesday, etc? For arguments sake, are you looking for all Mondays greater than the start date and earlier than the end date to have their corresponding values in column D? Whatevers in your A column will determine what way to go with the formula, also what version of Excel are you using?

 

[Jbooze]

Sorry. Yes, column A has the day of week in it and is checked against B11 in the tab that the formula resides in.

 

[scottylad2]

I possibly never phrased the question properley SUMPRODUCT((Configuration!A3:A370=B11)*(Configuration!C3:C370>=(T3)*(Configuration!C3:C370<=(U3)*(Configuration!D3:D370)))) do the days of the week in column A3:A370 have any direct relationship with with the actual dates in column C?

For example if the date in C3 is todays date, would A3 be the same as today as in Wednesday? If it is, is A3=C3? if not then you don't actually need that column included in your formula to count how many weekdays are in a range greater or less than and = a weekday

again, which version of Excel?

 

[Jbooze]

I am sorting by day of week in the sum that I need and then by a date range that will be entered in cells T3 and U3. A3's text value is derived from the same source as c3 with formula: =IF(C3=0,"",TEXT('Paste Data'!L2,"dddd")). It is the day of the date in C3. I am using Excel 2010.

Thanks.

 

[scottylad2]

Ok, this assumes your using office 2007 or later. Eitherway you can discount the days in column A, the weekday function used on your dates column will suffice to work out what days of the week are. Hope this helps

Excel Workbook
	G	H	I	J	K
3	Saturday	01/01/2011	7,727.00	Start Date	01/01/2011
4	Sunday	02/01/2011	3,305.00	End Date	08/02/2011
5	Monday	03/01/2011	6,784.00	Weekday	Monday
6	Tuesday	04/01/2011	8,895.00	Total	31,998.00
7	Wednesday	05/01/2011	5,733.00
8	Thursday	06/01/2011	3,408.00
9	Friday	07/01/2011	4,821.00
10	Saturday	08/01/2011	7,880.00
11	Sunday	09/01/2011	5,827.00
12	Monday	10/01/2011	6,863.00
13	Tuesday	11/01/2011	6,701.00
14	Wednesday	12/01/2011	1,005.00
15	Thursday	13/01/2011	2,502.00
16	Friday	14/01/2011	7,181.00
17	Saturday	15/01/2011	3,044.00
18	Sunday	16/01/2011	1,314.00
19	Monday	17/01/2011	4,712.00
20	Tuesday	18/01/2011	2,041.00
21	Wednesday	19/01/2011	5,040.00
22	Thursday	20/01/2011	6,285.00
23	Friday	21/01/2011	8,398.00
24	Saturday	22/01/2011	5,949.00
25	Sunday	23/01/2011	409.00
26	Monday	24/01/2011	7,708.00
27	Tuesday	25/01/2011	1,140.00
28	Wednesday	26/01/2011	3,559.00
29	Thursday	27/01/2011	701.00
30	Friday	28/01/2011	1,687.00
31	Saturday	29/01/2011	5,553.00
32	Sunday	30/01/2011	2,756.00
33	Monday	31/01/2011	4,486.00
34	Tuesday	01/02/2011	2,397.00
35	Wednesday	02/02/2011	6,989.00
36	Thursday	03/02/2011	7,434.00
37	Friday	04/02/2011	1,264.00
38	Saturday	05/02/2011	5,213.00
39	Sunday	06/02/2011	4,049.00
40	Monday	07/02/2011	1,445.00
Sheet1

You could also use the Sumifs function in 2007 or layer

 

[Jbooze]

Thanks for the reply. I am getting a 0 value for the answer when the range has values. My dates don't have the zero place holder (1/17/2011 instead of 01/17/2011). Would this cause the 0 answer?

 

[Jbooze]

My formula looks like this now: =SUMPRODUCT(--(WEEKDAY(Configuration!C3:C370,2)=1),--(Configuration!C3:C370>=K3),--(Configuration!C3:C370<=M4),Configuration!D3:D370)

 

[scottylad2]

Thanks for the reply. I am getting a 0 value for the answer when the range has values. My dates don't have the zero place holder (1/17/2011 instead of 01/17/2011). Would this cause the 0 answer?

are your dates imported? try in A1 =isnumber(B11) and see if you get a TRUE or FALSE, if its true then try the same on the dates in your Column C and the cells containing your date criteria. If any of them return False then your dates are Text and not real dates, but we can adjust for that. When you used the Weekday(function did the option appear as you were typing it into the Formula? if not then you need to add the analysis toolpack add in but i thought it was there as standard in 2010, the only other posible for it showing as 0 would be the sums to value, also can be checked with
isnumber

 

[scottylad2]

My formula looks like this now: =SUMPRODUCT(--(WEEKDAY(Configuration!C3:C370,2)=B11),--(Configuration!C3:C370>=K3),--(Configuration!C3:C370<=M4),Configuration!D3:D370)

The formula typed as is is only looking for Mondays, that was for my example, if you were checking against mondays and there were no values to sum then check against a day where there is sum values. In your formula, you had B11 as the source of the weekday to check, replace B11 Contents with a cell that has any number from 1 through to 7. 1 = monday, 2 tuesday 3 wednesday etc etc