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

Select all contiguous cells
Pressing Ctrl+* (asterisk) will select the "current region" - all contiguous cells in all directions.

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,209
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

ADVERTISEMENT

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

ADVERTISEMENT

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?
 
Learn Excel from Bill Jelen

Understanding data is crucial, and the easiest place to start is with Microsoft Excel.

Forum statistics

Threads
1,151,565
Messages
5,765,147
Members
425,263
Latest member
alcat

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