# 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

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

#### 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
1
Views
177
Replies
1
Views
118
Replies
3
Views
129
Replies
10
Views
588
Replies
11
Views
1K

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

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.

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