SUM(IF) not working

[jray9242]

I am modifying a spreadsheet and now the formulas are not working. Here is what the sheet looks like:

Field "N" is the field to be totaled on. I check the "Status" field and based on this, the bottom results in "Summary" Field "D" should add up. As you can see is that it isn't adding up and I can't figure out why?

Thanks for the help.

Worksheet

*

B

C

D

E

F

G

H

I

J

K

L

M

N

O

P

Q

R

S

MyCity

ABCD

CDEF

CMFD

Fire

*

TLO-Intermediate

*

MyCity

Awareness

ABCD

CDEF

*

*

*

*

*

HBFD

Fire

*

TLO-Intermediate

*

MyCity

Awareness

ABCD

CDEF

*

*

*

*

OCFA

Fire

*

TLO-Intermediate

*

MyCity

Awareness

ABCD

CDEF

*

*

*

*

*

OCFA

Fire

*

*

TLO-Intermediate

*

MyCity

Awareness

ABCD

CDEF

*

*

*

*

*

OFD

Fire

*

TLO-Intermediate

*

MyCity

Awareness

ABCD

CDEF

*

*

*

*

*

FPD

Law Enforcement

*

TLO-Intermediate

*

MyCity

Awareness

ABCD

CDEF

*

*

*

*

*

LBPD

Law Enforcement

*

TLO-Intermediate

*

MyCity

Awareness

ABCD

CDEF

*

*

*

*

*

NBPD

Law Enforcement

*

TLO-Intermediate

*

MyCity

Awareness

ABCD

CDEF

*

*

*

*

*

SAPD

Law Enforcement

*

TLO-Intermediate

*

MyCity

Awareness

ABCD

CDEF

*

*

*

*

*

MyCity

ABCD

CDEF

<colgroup><col style="font-weight:bold; width:30px; "><col style="width:69px;"><col style="width:51px;"><col style="width:108px;"><col style="width:49px;"><col style="width:41px;"><col style="width:107px;"><col style="width:192px;"><col style="width:89px;"><col style="width:71px;"><col style="width:71px;"><col style="width:55px;"><col style="width:80px;"><col style="width:50px;"><col style="width:68px;"><col style="width:84px;"><col style="width:45px;"><col style="width:45px;"><col style="width:53px;"></colgroup><tbody>
[TD="bgcolor: #cacaca, align: center"]1[/TD]
[TD="bgcolor: #c0c0c0"]Status[/TD]
[TD="bgcolor: #c0c0c0"]Agency[/TD]
[TD="bgcolor: #c0c0c0"]Discipline[/TD]
[TD="bgcolor: #008080"]Trained[/TD]
[TD="bgcolor: #008080"]Hours[/TD]
[TD="bgcolor: #c0c0c0"]Course[/TD]
[TD="bgcolor: #008080"]Topic[/TD]
[TD="bgcolor: #c0c0c0"]Course_Loc[/TD]
[TD="bgcolor: #c0c0c0"]Start_Date[/TD]
[TD="bgcolor: #c0c0c0"]Level[/TD]
[TD="bgcolor: #c0c0c0"]Sponsor[/TD]
[TD="bgcolor: #c0c0c0"]Company[/TD]
[TD="bgcolor: #c0c0c0"]Tuition[/TD]
[TD="bgcolor: #008080, align: center"]Delivery
Cost UASI[/TD]
[TD="bgcolor: #008080, align: center"]Delivery
Cost SHSGP[/TD]
[TD="bgcolor: #008080, align: center"]LE UASI BF/OT[/TD]
[TD="bgcolor: #008080, align: center"]Fire UASI BF/OT[/TD]
[TD="bgcolor: #008080, align: center"]LE SHSGP BF/OT[/TD]

[TD="bgcolor: #cacaca, align: center"]2[/TD]
[TD="bgcolor: #ffff00"]Pending[/TD]
[TD="bgcolor: #00ccff"]*[/TD]
[TD="bgcolor: #00ccff"]Delivery[/TD]
[TD="bgcolor: #00ccff"]*[/TD]
[TD="bgcolor: #00ccff, align: right"]24[/TD]
[TD="bgcolor: #00ccff"]TLO-Intermediate[/TD]
[TD="bgcolor: #00ccff"]*[/TD]

[TD="bgcolor: #00ccff, align: right"]11/29/2011[/TD]
[TD="bgcolor: #00ccff"]Awareness[/TD]

[TD="bgcolor: #00ccff, align: right"]14,950[/TD]
[TD="bgcolor: #00ccff, align: right"]14,950[/TD]
[TD="bgcolor: #00ccff"]*[/TD]
[TD="bgcolor: #00ccff"]*[/TD]
[TD="bgcolor: #00ccff"]*[/TD]
[TD="bgcolor: #00ccff"]*[/TD]

[TD="bgcolor: #cacaca, align: center"]3[/TD]
[TD="bgcolor: #00b050"]Completed[/TD]

[TD="align: right"]2[/TD]

[TD="align: right"]11/29/2011[/TD]

[TD="align: right"]2,559 [/TD]

[TD="bgcolor: #cacaca, align: center"]4[/TD]
[TD="bgcolor: #00b050"]Completed[/TD]

[TD="align: right"]1[/TD]

[TD="align: right"]11/29/2011[/TD]

[TD="bgcolor: #ece9d8"]*[/TD]

[TD="align: right"]1,594 [/TD]

[TD="bgcolor: #cacaca, align: center"]5[/TD]
[TD="bgcolor: #00b050"]Completed[/TD]

[TD="align: right"]2[/TD]

[TD="align: right"]11/29/2011[/TD]

[TD="align: right"]2,071 [/TD]

[TD="bgcolor: #cacaca, align: center"]6[/TD]
[TD="bgcolor: #00b050"]Completed[/TD]

[TD="align: right"]11/29/2011[/TD]

[TD="align: right"]1,880 [/TD]

[TD="bgcolor: #cacaca, align: center"]7[/TD]
[TD="bgcolor: #00b050"]Completed[/TD]

[TD="align: right"]3[/TD]

[TD="align: right"]11/29/2011[/TD]

[TD="align: right"]4,543 [/TD]

[TD="bgcolor: #cacaca, align: center"]8[/TD]
[TD="bgcolor: #00b050"]Completed[/TD]

[TD="align: right"]3[/TD]

[TD="align: right"]11/29/2011[/TD]

[TD="align: right"]1,510 [/TD]

[TD="bgcolor: #cacaca, align: center"]9[/TD]
[TD="bgcolor: #ffff00"]Pending[/TD]

[TD="align: right"]1[/TD]

[TD="align: right"]11/29/2011[/TD]

[TD="align: right"]1,500 [/TD]

[TD="bgcolor: #cacaca, align: center"]10[/TD]
[TD="bgcolor: #ffff00"]Pending[/TD]

[TD="align: right"]1[/TD]

[TD="align: right"]11/29/2011[/TD]

[TD="align: right"]1,500 [/TD]

[TD="bgcolor: #cacaca, align: center"]11
[/TD]
[TD="bgcolor: #ffff00"]Pending[/TD]

[TD="align: right"]1[/TD]

[TD="align: right"]11/29/2011[/TD]

[TD="align: right"]1,500 [/TD]

[TD="bgcolor: #cacaca, align: center"]12
[/TD]
[TD="bgcolor: #00b050"]Completed[/TD]
[TD="bgcolor: #00ccff"]*[/TD]
[TD="bgcolor: #00ccff"]Delivery[/TD]
[TD="bgcolor: #00ccff"]*[/TD]
[TD="bgcolor: #00ccff, align: right"]8[/TD]
[TD="bgcolor: #00ccff"]TLO-Basic[/TD]
[TD="bgcolor: #00ccff"]*[/TD]

[TD="bgcolor: #00ccff, align: right"]12/2/2011[/TD]
[TD="bgcolor: #00ccff"]Awareness[/TD]

[TD="bgcolor: #00ccff"]*[/TD]
[TD="bgcolor: #00ccff"]*[/TD]
[TD="bgcolor: #00ccff"]*[/TD]
[TD="bgcolor: #00ccff"]*[/TD]
[TD="bgcolor: #00ccff"]*[/TD]
[TD="bgcolor: #00ccff"]*[/TD]

</tbody>

Excel tables to the web >> Excel Jeanie HTML 4

Summary

*	B	C	D	E	F	G
*
*

<colgroup><col style="font-weight:bold; width:30px; "><col style="width:155px;"><col style="width:201px;"><col style="width:73px;"><col style="width:87px;"><col style="width:87px;"><col style="width:87px;"></colgroup><tbody>
[TD="bgcolor: #cacaca, align: center"]6[/TD]
[TD="bgcolor: #c0c0c0"]*[/TD]
[TD="bgcolor: #c0c0c0, align: center"]Total[/TD]
[TD="bgcolor: #c0c0c0, align: center"]Tution[/TD]
[TD="bgcolor: #c0c0c0"]*[/TD]
[TD="bgcolor: #c0c0c0, align: center"]UASI[/TD]
[TD="bgcolor: #c0c0c0, align: center"]SHGP[/TD]

[TD="bgcolor: #cacaca, align: center"]7[/TD]
[TD="bgcolor: #ccffcc"]Encumbered[/TD]
[TD="align: right"]$4,500.00[/TD]
[TD="align: right"]$6,700.00[/TD]

[TD="align: right"]$4,500.00[/TD]
[TD="align: right"]$0.00[/TD]

[TD="bgcolor: #cacaca, align: center"]8[/TD]
[TD="bgcolor: #ffff99"]Expended[/TD]
[TD="align: right"]$11,598.31[/TD]
[TD="align: right"]$6,700.00[/TD]

[TD="align: right"]$9,727.19[/TD]
[TD="align: right"]$1,871.12[/TD]

</tbody>

Spreadsheet Formulas

Cell Formula C7 {=SUM(IF(Status="Pending",Total))} D7 {=SUM(IF(Status="Pending",Tution))} F7 {=SUM(IF(Status="Pending",OT))} G7 {=SUM(IF(Status="Pending",Backfill))} C8 {=SUM(IF(Status="Completed",Total))} D8 {=SUM(IF(Status="Completed",Tution))} F8 {=SUM(IF(Status="Completed",OT))} G8 {=SUM(IF(Status="Completed",Backfill))} <tbody> </tbody>

Formula Array: Produce enclosing { } by entering formula with CTRL+SHIFT+ENTER!

<tbody>
</tbody>

Excel tables to the web >> Excel Jeanie HTML 4

 

[dermie_72]

Have you tried adjusting the formula to:
=sumif(status,"Pending",Total)
or =sumif(Range,criteria,sum range).

See if that returns the value you are after.

The other thing to check would be the Range Names, to ensure that they are Equal. IE start in the same row's, end in the same rows, that sort of thing. If the ranges are different it doesn't sum the way it should.

Hope that helps.

 

[jray9242]

Here's what I did and thanks for the help. It's been a while so please treat me as a noob. As you can see I changed the formula and got one of the values correct. Yes the Name Range was off but it's not picking up the right criteria for Pending and Complete.

Encumbered means Pending and Expended means Complete. I didn't write this but tasked to change it.

Thanks again!

Jim

Worksheet

*

B

C

D

E

F

G

I

N

O

P

Q

R

S

MyCity

*

Delivery

*

*

TLO-Advance

MyCity

*

*

*

*

*

*

Delivery

*

*

TLO-Advance

MyCity

*

*

*

*

*

<colgroup><col style="font-weight:bold; width:30px; "><col style="width:69px;"><col style="width:51px;"><col style="width:108px;"><col style="width:49px;"><col style="width:41px;"><col style="width:107px;"><col style="width:89px;"><col style="width:50px;"><col style="width:68px;"><col style="width:84px;"><col style="width:45px;"><col style="width:45px;"><col style="width:53px;"></colgroup><tbody>
[TD="bgcolor: #cacaca, align: center"]1[/TD]
[TD="bgcolor: #c0c0c0"]Status[/TD]
[TD="bgcolor: #c0c0c0"]Agency[/TD]
[TD="bgcolor: #c0c0c0"]Discipline[/TD]
[TD="bgcolor: #008080"]Trained[/TD]
[TD="bgcolor: #008080"]Hours[/TD]
[TD="bgcolor: #c0c0c0"]Course[/TD]
[TD="bgcolor: #c0c0c0"]Course_Loc[/TD]
[TD="bgcolor: #c0c0c0"]Tuition[/TD]
[TD="bgcolor: #008080, align: center"]Delivery
Cost UASI[/TD]
[TD="bgcolor: #008080, align: center"]Delivery
Cost SHSGP[/TD]
[TD="bgcolor: #008080, align: center"]LE UASI BF/OT[/TD]
[TD="bgcolor: #008080, align: center"]Fire UASI BF/OT[/TD]
[TD="bgcolor: #008080, align: center"]LE SHSGP BF/OT
[/TD]

[TD="bgcolor: #cacaca, align: center"]2[/TD]
[TD="bgcolor: #00b050"]Completed[/TD]
[TD="bgcolor: #00ccff"]*[/TD]
[TD="bgcolor: #00ccff"]Delivery[/TD]
[TD="bgcolor: #00ccff"]*[/TD]
[TD="bgcolor: #00ccff, align: right"]24[/TD]
[TD="bgcolor: #00ccff"]TLO-Intermediate[/TD]

[TD="bgcolor: #00ccff, align: right"]14,950[/TD]
[TD="bgcolor: #00ccff, align: right"]14,950[/TD]
[TD="bgcolor: #00ccff"]*[/TD]
[TD="bgcolor: #00ccff"]*[/TD]
[TD="bgcolor: #00ccff"]*[/TD]
[TD="bgcolor: #00ccff"]*[/TD]

[TD="bgcolor: #cacaca, align: center"]23[/TD]
[TD="bgcolor: #ffff00"]Pending[/TD]

[TD="align: right"]6,700 [/TD]

[TD="bgcolor: #cacaca, align: center"]24[/TD]
[TD="bgcolor: #00b050"]Completed[/TD]

[TD="align: right"]6,700 [/TD]

</tbody>

Excel tables to the web >> Excel Jeanie HTML 4

Summary

*	B	C	D	E	F	G
*
*

<colgroup><col style="font-weight:bold; width:30px; "><col style="width:155px;"><col style="width:201px;"><col style="width:72px;"><col style="width:87px;"><col style="width:87px;"><col style="width:87px;"></colgroup><tbody>
[TD="bgcolor: #cacaca, align: center"]6[/TD]
[TD="bgcolor: #c0c0c0"]*[/TD]
[TD="bgcolor: #c0c0c0, align: center"]Total[/TD]
[TD="bgcolor: #c0c0c0, align: center"]Tution[/TD]
[TD="bgcolor: #c0c0c0"]*[/TD]
[TD="bgcolor: #c0c0c0, align: center"]UASI[/TD]
[TD="bgcolor: #c0c0c0, align: center"]SHGP[/TD]

[TD="bgcolor: #cacaca, align: center"]7[/TD]
[TD="bgcolor: #ccffcc"]Encumbered
[/TD]
[TD="align: right"]$4,500.00[/TD]
[TD="align: right"]$0.00[/TD]

[TD="align: right"]$4,500.00[/TD]
[TD="align: right"]$0.00[/TD]

[TD="bgcolor: #cacaca, align: center"]8[/TD]
[TD="bgcolor: #ffff99"]Expended
[/TD]
[TD="align: right"]$11,598.31[/TD]
[TD="align: right"]$14,950.00[/TD]

[TD="align: right"]$9,727.19[/TD]
[TD="align: right"]$1,871.12[/TD]

</tbody>

Spreadsheet Formulas

Cell Formula C7 {=SUM(IF(Status="Pending",Total))} D7 =SUMIF(Status,"Pending",Tution) F7 {=SUM(IF(Status="Pending",OT))} G7 {=SUM(IF(Status="Pending",Backfill))} C8 {=SUM(IF(Status="Completed",Total))} D8 =SUMIF(Status,"Completed",Tution) F8 {=SUM(IF(Status="Completed",OT))} G8 {=SUM(IF(Status="Completed",Backfill))} <tbody> </tbody>

Formula Array: Produce enclosing { } by entering formula with CTRL+SHIFT+ENTER!

<tbody>
</tbody>

Excel tables to the web >> Excel Jeanie HTML 4

 

[jray9242]

Problem Solved! Once again it was a name range problem and I thought I had checked them all but didn't

Thanks again for all the help!

Jim