Formula Problems...

sstorm

Board Regular
Joined
Nov 5, 2003
Messages
76
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!!!
 

Excel Facts

Which came first: VisiCalc or Lotus 1-2-3?
Dan Bricklin and Bob Frankston debuted VisiCalc in 1979 as a Visible Calculator. Lotus 1-2-3 debuted in the early 1980's, from Mitch Kapor.

Brian from Maui

MrExcel MVP
Joined
Feb 16, 2002
Messages
8,459
sstorm said:
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

MrExcel MVP
Joined
Feb 14, 2002
Messages
85,210
Brian from Maui said:
sstorm said:
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

Board Regular
Joined
Nov 5, 2003
Messages
76
=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

MrExcel MVP
Joined
Feb 16, 2002
Messages
8,459
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
ABCDE
110
2centralillinois60connect1/9/200410
Sheet1


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

sstorm

Board Regular
Joined
Nov 5, 2003
Messages
76
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

MrExcel MVP
Joined
Feb 16, 2002
Messages
8,459
sstorm said:
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.... :devilish:

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

Board Regular
Joined
Nov 5, 2003
Messages
76
=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

Board Regular
Joined
Nov 5, 2003
Messages
76
=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

Legend
Joined
Sep 3, 2002
Messages
10,473
sstorm said:
=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?
 

Forum statistics

Threads
1,186,169
Messages
5,956,342
Members
438,247
Latest member
UZev

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
Top