# Formula Problems...

#### sstorm

##### Board Regular
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
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

##### MrExcel MVP
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
=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
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
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
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....

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
=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
=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
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?

Replies
8
Views
145
Replies
9
Views
85
Replies
11
Views
178
Replies
21
Views
301
Replies
3
Views
59

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.

### Which adblocker are you using?

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

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