Formula Problems...

[sstorm]

Here is my formula...

=SUM(IF(('FUTURE DAYS'!$H$2:$H$3000="Central Illinois 60")*('FUTURE DAYS'!$C$2:$C$3000="Connect")*('FUTURE DAYS'!$H$2:$H$3000=TODAY()),'FUTURE DAYS'!$E$2:$E$3000))

What I am trying to do is sum column "E" if it meets all the conditions listed above. I have tried this as an array also. This formula keeps returning zero and the answer should be 48. Any help would be appreciated. Thanks!!!

 

[Brian from Maui]

Here is my formula...

=SUM(IF(('FUTURE DAYS'!$H$2:$H$3000="Central Illinois 60")*('FUTURE DAYS'!$C$2:$C$3000="Connect")*('FUTURE DAYS'!$H$2:$H$3000=TODAY()),'FUTURE DAYS'!$E$2:$E$3000))

What I am trying to do is sum column "E" if it meets all the conditions listed above. I have tried this as an array also. This formula keeps returning zero and the answer should be 48. Any help would be appreciated. Thanks!!!

You have the same ranges for today() and Central illinois 60 which will always result in 0

 

[Aladin Akyurek]

Here is my formula...

=SUM(IF(('FUTURE DAYS'!$H$2:$H$3000="Central Illinois 60")*('FUTURE DAYS'!$C$2:$C$3000="Connect")*('FUTURE DAYS'!$H$2:$H$3000=TODAY()),'FUTURE DAYS'!$E$2:$E$3000))

What I am trying to do is sum column "E" if it meets all the conditions listed above. I have tried this as an array also. This formula keeps returning zero and the answer should be 48. Any help would be appreciated. Thanks!!!

You have the same ranges for today() and Central illinois 60 which will always result in 0

The earlier post

http://www.mrexcel.com/board2/viewtopic.php?p=347901&highlight=#347901

has different ranges indeed.

 

[sstorm]

=SUM(IF(('FUTURE DAYS'!$H$2:$H$3000="Central Illinois 60")*('FUTURE DAYS'!$C$2:$C$3000="Connect")*('FUTURE DAYS'!$D$2:$D$3000=TODAY()),'FUTURE DAYS'!$E$2:$E$3000))

Ok...now I have changed the range for TODAY and I still get zero. Am I missing something?

 

[Brian from Maui]

Change the range and worksheets


=SUMPRODUCT(--($H$2:$H$3000="Central Illinois 60"),--($C$2:$C$3000="Connect"),--($D$2:$D$3000=TODAY()),$E$2:$E$3000)

Book1
	A	B	C	D	E
1	10
2		centralillinois60	connect	1/9/2004	10
Sheet1

The Sumif function you're trying to use needs to be entered as an array CTRL+SHIFT+ENTER

 

[sstorm]

Here is my formula:


=SUM(IF(('FUTURE DAYS'!$H$2:$H$3000="Central Illinois 60")*('FUTURE DAYS'!$C$2:$C$3000="Connect")*('FUTURE DAYS'!$D$2:$D$3000=$AB7),0,'FUTURE DAYS'!$E$2:$E$3000))

The problem that I am having is that now it sums the whole column E. I only want it to sum the cells in E that meet all the criteria---NOT the whole thing. HELP HELP HELP!!!

Stacy

 

[Brian from Maui]

Here is my formula:


=SUM(IF(('FUTURE DAYS'!$H$2:$H$3000="Central Illinois 60")*('FUTURE DAYS'!$C$2:$C$3000="Connect")*('FUTURE DAYS'!$D$2:$D$3000=$AB7),0,'FUTURE DAYS'!$E$2:$E$3000))

The problem that I am having is that now it sums the whole column E. I only want it to sum the cells in E that meet all the criteria---NOT the whole thing. HELP HELP HELP!!!

Stacy

Use the Sumproduct....

The Sum would look something like,

=SUM(IF(H2:H30="CENTRAL",IF(C2:C30="CONNECT",IF(D2:D30=TODAY(),E2:E30))))

entered using CSE

 

[sstorm]

=SUMPRODUCT(--('FUTURE DAYS'!H2:H3000="Central Illinois 60"),--('FUTURE DAYS'!C2:C3000="Connect"),--('FUTURE DAYS'!D2:D3000=AB2),'FUTURE DAYS'!E2:E3000)

This does not work, it provides me with an N/A.

 

[sstorm]

=SUM(IF('FUTURE DAYS'!$H$2:$H$3000="Central Illinois 60",IF('FUTURE DAYS'!$C$2:$C$3000="Connect",IF('FUTURE DAYS'!$D$2:$D$3000=AB2,'FUTURE DAYS'!$E$2:$E$3000))))

This is not working for us either. We want the formula to read from a seperate worksheet in the workbook and sum only those cells in column E that correspond with the other criteria.

 

[just_jon]

=SUMPRODUCT(--('FUTURE DAYS'!H2:H3000="Central Illinois 60"),--('FUTURE DAYS'!C2:C3000="Connect"),--('FUTURE DAYS'!D2:D3000=AB2),'FUTURE DAYS'!E2:E3000)

This does not work, it provides me with an N/A.

Do you have a worksheet named [ exactly ] FUTURE DAYS? What type of data is in E2:E3000?