IF Statement Challenge, just can't get the formula to work just right.

boldcode · Mar 9, 2011

Hello,

I am trying to come up with a formula that would provide me with the results that I am looking for. It's a bit of a challenge as I tried using the "IF Function" in a formula and the formula did not work. Below I am providing you with exactly what my spreadsheet looks like:

<table class="MsoNormalTable" style="width: 483.05pt; margin-left: 4.65pt; border-collapse: collapse;" border="0" cellpadding="0" cellspacing="0" width="644"> <tbody><tr style="height: 14.7pt;"> <td style="width: 42.4pt; border: 1pt solid windowtext; background: none repeat scroll 0% 0% white; padding: 0in 5.4pt; height: 14.7pt;" valign="bottom" width="57" nowrap="nowrap"> ROW 1
</td> <td style="width: 51.85pt; border-width: 1pt 1pt 1pt medium; border-style: solid solid solid none; border-color: windowtext windowtext windowtext -moz-use-text-color; background: none repeat scroll 0% 0% black; padding: 0in 5.4pt; height: 14.7pt;" valign="bottom" width="69" nowrap="nowrap"> Column B
</td> <td style="width: 82.45pt; border-width: 1pt 1pt 1pt medium; border-style: solid solid solid none; border-color: windowtext windowtext windowtext -moz-use-text-color; background: none repeat scroll 0% 0% black; padding: 0in 5.4pt; height: 14.7pt;" valign="bottom" width="110" nowrap="nowrap"> Column C
</td> <td style="width: 73.05pt; border-width: 1pt 1pt 1pt medium; border-style: solid solid solid none; border-color: windowtext windowtext windowtext -moz-use-text-color; background: none repeat scroll 0% 0% black; padding: 0in 5.4pt; height: 14.7pt;" valign="bottom" width="97" nowrap="nowrap"> Column D
</td> <td style="width: 70.7pt; border-width: 1pt 1pt 1pt medium; border-style: solid solid solid none; border-color: windowtext windowtext windowtext -moz-use-text-color; background: none repeat scroll 0% 0% black; padding: 0in 5.4pt; height: 14.7pt;" valign="bottom" width="94" nowrap="nowrap"> Column F
</td> <td style="width: 62.45pt; border-width: 1pt 1pt 1pt medium; border-style: solid solid solid none; border-color: windowtext windowtext windowtext -moz-use-text-color; background: none repeat scroll 0% 0% black; padding: 0in 5.4pt; height: 14.7pt;" valign="bottom" width="83" nowrap="nowrap"> Column G
</td> <td style="width: 51.85pt; border-width: 1pt 1pt 1pt medium; border-style: solid solid solid none; border-color: windowtext windowtext windowtext -moz-use-text-color; background: none repeat scroll 0% 0% black; padding: 0in 5.4pt; height: 14.7pt;" valign="bottom" width="69" nowrap="nowrap"> Column H
</td> <td style="width: 48.3pt; border-width: 1pt 1pt 1pt medium; border-style: solid solid solid none; border-color: windowtext windowtext windowtext -moz-use-text-color; background: none repeat scroll 0% 0% black; padding: 0in 5.4pt; height: 14.7pt;" valign="bottom" width="64" nowrap="nowrap"> Column I
</td> </tr> <tr style="height: 14.7pt;"> <td style="width: 42.4pt; border-width: medium 1pt 1pt; border-style: none solid solid; border-color: -moz-use-text-color windowtext windowtext; background: none repeat scroll 0% 0% black; padding: 0in 5.4pt; height: 14.7pt;" valign="bottom" width="57" nowrap="nowrap"> ROW 1
</td> <td colspan="3" style="width: 207.3pt; border-width: medium 1pt 1pt medium; border-style: none solid solid none; border-color: -moz-use-text-color windowtext windowtext -moz-use-text-color; padding: 0in 5.4pt; height: 14.7pt;" valign="bottom" width="276" nowrap="nowrap">

Budget

</td> <td colspan="4" style="width: 233.25pt; border-width: medium 1pt 1pt medium; border-style: none solid solid none; border-color: -moz-use-text-color windowtext windowtext -moz-use-text-color; padding: 0in 5.4pt; height: 14.7pt;" valign="bottom" width="311" nowrap="nowrap">

Monthly Transactions

</td> </tr> <tr style="height: 14.7pt;"> <td style="width: 42.4pt; border-width: medium 1pt 1pt; border-style: none solid solid; border-color: -moz-use-text-color windowtext windowtext; background: none repeat scroll 0% 0% black; padding: 0in 5.4pt; height: 14.7pt;" valign="bottom" width="57" nowrap="nowrap"> ROW 2
</td> <td style="width: 51.85pt; border-width: medium 1pt 1pt medium; border-style: none solid solid none; border-color: -moz-use-text-color windowtext windowtext -moz-use-text-color; padding: 0in 5.4pt; height: 14.7pt;" valign="bottom" width="69" nowrap="nowrap"> FOR
</td> <td style="width: 82.45pt; border-width: medium 1pt 1pt medium; border-style: none solid solid none; border-color: -moz-use-text-color windowtext windowtext -moz-use-text-color; padding: 0in 5.4pt; height: 14.7pt;" valign="bottom" width="110" nowrap="nowrap"> Allotted Budget
</td> <td style="width: 73.05pt; border-width: medium 1pt 1pt medium; border-style: none solid solid none; border-color: -moz-use-text-color windowtext windowtext -moz-use-text-color; padding: 0in 5.4pt; height: 14.7pt;" valign="bottom" width="97" nowrap="nowrap"> Amount Spent
</td> <td style="width: 70.7pt; border-width: medium 1pt 1pt medium; border-style: none solid solid none; border-color: -moz-use-text-color windowtext windowtext -moz-use-text-color; padding: 0in 5.4pt; height: 14.7pt;" valign="bottom" width="94" nowrap="nowrap"> Expense Type
</td> <td style="width: 62.45pt; border-width: medium 1pt 1pt medium; border-style: none solid solid none; border-color: -moz-use-text-color windowtext windowtext -moz-use-text-color; padding: 0in 5.4pt; height: 14.7pt;" valign="bottom" width="83" nowrap="nowrap"> Description
</td> <td style="width: 51.85pt; border-width: medium 1pt 1pt medium; border-style: none solid solid none; border-color: -moz-use-text-color windowtext windowtext -moz-use-text-color; padding: 0in 5.4pt; height: 14.7pt;" valign="bottom" width="69" nowrap="nowrap">

JAN

</td> <td style="width: 48.3pt; border-width: medium 1pt 1pt medium; border-style: none solid solid none; border-color: -moz-use-text-color windowtext windowtext -moz-use-text-color; padding: 0in 5.4pt; height: 14.7pt;" valign="bottom" width="64" nowrap="nowrap">

FEB

</td> </tr> <tr style="height: 14.7pt;"> <td style="width: 42.4pt; border-width: medium 1pt 1pt; border-style: none solid solid; border-color: -moz-use-text-color windowtext windowtext; background: none repeat scroll 0% 0% black; padding: 0in 5.4pt; height: 14.7pt;" valign="bottom" width="57" nowrap="nowrap"> ROW 3
</td> <td style="width: 51.85pt; border-width: medium 1pt 1pt medium; border-style: none solid solid none; border-color: -moz-use-text-color windowtext windowtext -moz-use-text-color; padding: 0in 5.4pt; height: 14.7pt;" valign="bottom" width="69" nowrap="nowrap"> MA
</td> <td style="width: 82.45pt; border-width: medium 1pt 1pt medium; border-style: none solid solid none; border-color: -moz-use-text-color windowtext windowtext -moz-use-text-color; padding: 0in 5.4pt; height: 14.7pt;" valign="bottom" width="110" nowrap="nowrap">

$50,000

</td> <td style="width: 73.05pt; border-width: medium 1pt 1pt medium; border-style: none solid solid none; border-color: -moz-use-text-color windowtext windowtext -moz-use-text-color; padding: 0in 5.4pt; height: 14.7pt;" valign="bottom" width="97" nowrap="nowrap">

</td> <td style="width: 70.7pt; border-width: medium 1pt 1pt medium; border-style: none solid solid none; border-color: -moz-use-text-color windowtext windowtext -moz-use-text-color; padding: 0in 5.4pt; height: 14.7pt;" valign="bottom" width="94" nowrap="nowrap"> MA
</td> <td style="width: 62.45pt; border-width: medium 1pt 1pt medium; border-style: none solid solid none; border-color: -moz-use-text-color windowtext windowtext -moz-use-text-color; padding: 0in 5.4pt; height: 14.7pt;" valign="bottom" width="83" nowrap="nowrap"> blah..blah
</td> <td style="width: 51.85pt; border-width: medium 1pt 1pt medium; border-style: none solid solid none; border-color: -moz-use-text-color windowtext windowtext -moz-use-text-color; padding: 0in 5.4pt; height: 14.7pt;" valign="bottom" width="69" nowrap="nowrap">

$30,000

</td> <td style="width: 48.3pt; border-width: medium 1pt 1pt medium; border-style: none solid solid none; border-color: -moz-use-text-color windowtext windowtext -moz-use-text-color; padding: 0in 5.4pt; height: 14.7pt;" valign="bottom" width="64" nowrap="nowrap">

$5,000

</td> </tr> <tr style="height: 14.7pt;"> <td style="width: 42.4pt; border-width: medium 1pt 1pt; border-style: none solid solid; border-color: -moz-use-text-color windowtext windowtext; background: none repeat scroll 0% 0% black; padding: 0in 5.4pt; height: 14.7pt;" valign="bottom" width="57" nowrap="nowrap"> ROW 4
</td> <td style="width: 51.85pt; border-width: medium 1pt 1pt medium; border-style: none solid solid none; border-color: -moz-use-text-color windowtext windowtext -moz-use-text-color; padding: 0in 5.4pt; height: 14.7pt;" valign="bottom" width="69" nowrap="nowrap">
</td> <td style="width: 82.45pt; border-width: medium 1pt 1pt medium; border-style: none solid solid none; border-color: -moz-use-text-color windowtext windowtext -moz-use-text-color; padding: 0in 5.4pt; height: 14.7pt;" valign="bottom" width="110" nowrap="nowrap">
</td> <td style="width: 73.05pt; border-width: medium 1pt 1pt medium; border-style: none solid solid none; border-color: -moz-use-text-color windowtext windowtext -moz-use-text-color; padding: 0in 5.4pt; height: 14.7pt;" valign="bottom" width="97" nowrap="nowrap">
</td> <td style="width: 70.7pt; border-width: medium 1pt 1pt medium; border-style: none solid solid none; border-color: -moz-use-text-color windowtext windowtext -moz-use-text-color; padding: 0in 5.4pt; height: 14.7pt;" valign="bottom" width="94" nowrap="nowrap"> MA
</td> <td style="width: 62.45pt; border-width: medium 1pt 1pt medium; border-style: none solid solid none; border-color: -moz-use-text-color windowtext windowtext -moz-use-text-color; padding: 0in 5.4pt; height: 14.7pt;" valign="bottom" width="83" nowrap="nowrap"> blah..blah
</td> <td style="width: 51.85pt; border-width: medium 1pt 1pt medium; border-style: none solid solid none; border-color: -moz-use-text-color windowtext windowtext -moz-use-text-color; padding: 0in 5.4pt; height: 14.7pt;" valign="bottom" width="69" nowrap="nowrap">

</td> <td style="width: 48.3pt; border-width: medium 1pt 1pt medium; border-style: none solid solid none; border-color: -moz-use-text-color windowtext windowtext -moz-use-text-color; padding: 0in 5.4pt; height: 14.7pt;" valign="bottom" width="64" nowrap="nowrap">

$10,000

</td> </tr> <tr style="height: 14.7pt;"> <td style="width: 42.4pt; border-width: medium 1pt 1pt; border-style: none solid solid; border-color: -moz-use-text-color windowtext windowtext; background: none repeat scroll 0% 0% black; padding: 0in 5.4pt; height: 14.7pt;" valign="bottom" width="57" nowrap="nowrap"> ROW 5
</td> <td style="width: 51.85pt; border-width: medium 1pt 1pt medium; border-style: none solid solid none; border-color: -moz-use-text-color windowtext windowtext -moz-use-text-color; padding: 0in 5.4pt; height: 14.7pt;" valign="bottom" width="69" nowrap="nowrap">
</td> <td style="width: 82.45pt; border-width: medium 1pt 1pt medium; border-style: none solid solid none; border-color: -moz-use-text-color windowtext windowtext -moz-use-text-color; padding: 0in 5.4pt; height: 14.7pt;" valign="bottom" width="110" nowrap="nowrap">
</td> <td style="width: 73.05pt; border-width: medium 1pt 1pt medium; border-style: none solid solid none; border-color: -moz-use-text-color windowtext windowtext -moz-use-text-color; padding: 0in 5.4pt; height: 14.7pt;" valign="bottom" width="97" nowrap="nowrap">
</td> <td style="width: 70.7pt; border-width: medium 1pt 1pt medium; border-style: none solid solid none; border-color: -moz-use-text-color windowtext windowtext -moz-use-text-color; padding: 0in 5.4pt; height: 14.7pt;" valign="bottom" width="94" nowrap="nowrap"> SP
</td> <td style="width: 62.45pt; border-width: medium 1pt 1pt medium; border-style: none solid solid none; border-color: -moz-use-text-color windowtext windowtext -moz-use-text-color; padding: 0in 5.4pt; height: 14.7pt;" valign="bottom" width="83" nowrap="nowrap"> blah..blah
</td> <td style="width: 51.85pt; border-width: medium 1pt 1pt medium; border-style: none solid solid none; border-color: -moz-use-text-color windowtext windowtext -moz-use-text-color; padding: 0in 5.4pt; height: 14.7pt;" valign="bottom" width="69" nowrap="nowrap">

</td> <td style="width: 48.3pt; border-width: medium 1pt 1pt medium; border-style: none solid solid none; border-color: -moz-use-text-color windowtext windowtext -moz-use-text-color; padding: 0in 5.4pt; height: 14.7pt;" valign="bottom" width="64" nowrap="nowrap">

</td> </tr> </tbody></table>

The formula is going to go into cell D3 under Amount Spent. The first thing I want to do is SUM the amounts under columns JAN and FEB that are attributed to “MA” under the column Expense Type and show the result in cell D3. In addition, and here is the tricky part, I only want the result to be shown in cell D3 if Sum of Amounts attributed to MA are Less Than or Equal To the amount in “cell C3 which in this case is $50,000.” If the Sum of Amount attributed to MA are Greater Than the amount in “cell C3 which in this case is $50,000,” then what I want to show is the following text in cell D3:
“Exceeds Allotted Budget”
That’s all that I am looking to do. I just can get the formula down right.
Let me know if you need further explanation.
Thanks in Advance,
Anthony Salazar

Trouttrap2 · Mar 9, 2011

I'm sure there are more eloquent ways of doing it. I'm assuming you are using Excel 2003. Try this:

=IF(SUMIF(F:F,B3,H:H)+SUMIF(F:F,B3,I:I)<=C3,SUMIF(F:F,B3,H:H)+SUMIF(F:F,B3,I:I),"Exceeds Alotted Budget")

boldcode · Mar 9, 2011

Thanks Trouttrap2! It seems to work, I'll let you know if withstands a few tests.

IF Statement Challenge, just can't get the formula to work just right.

boldcode

Active Member

Excel Facts

Trouttrap2

Well-known Member

boldcode

Active Member

Similar threads

Forum statistics

Share this page

IF Statement Challenge, just can't get the formula to work just right.

boldcode

Active Member

Excel Facts

Trouttrap2

Well-known Member

boldcode

Active Member

Similar threads

Forum statistics

Share this page

We've detected that you are using an adblocker.

Which adblocker are you using?

Disable AdBlock

Disable AdBlock Plus

Disable uBlock Origin

Disable uBlock