Help!!!! This formula is driving me crazy!!

[siasarma]

Hi! I am trying to use the SUMIF Function and this formula is not working. I have most of it right. I want to SUMIF and I have the range right and the criteria right, but then for the sum range I want to be able to subtract a group of numbers. I cant figure out how to subtract them. It adds them just fine. Here's my formula... This part I want subtracted.
=SUMIF('Credit Card'!D70:D92,'Expense Data'!D4,'Credit Card'!C70-'Credit Card'!C71) I actually have a whole range of number like in the next formula, I was just trying to get anything to work in this one.

<meta http-equiv="Content-Type" content="text/html; charset=utf-8"><meta name="ProgId" content="Word.Document"><meta name="Generator" content="Microsoft Word 11"><meta name="Originator" content="Microsoft Word 11"><link rel="File-List" href="file:///C:%5CDOCUME%7E1%5CSiah%5CLOCALS%7E1%5CTemp%5Cmsohtml1%5C01%5Cclip_filelist.xml"><!--[if gte mso 9]><xml> <w:WordDocument> <w:View>Normal</w:View> <w:Zoom>0</w:Zoom> <w:PunctuationKerning/> <w:ValidateAgainstSchemas/> <w:SaveIfXMLInvalid>false</w:SaveIfXMLInvalid> <w:IgnoreMixedContent>false</w:IgnoreMixedContent> <w:AlwaysShowPlaceholderText>false</w:AlwaysShowPlaceholderText> <w:Compatibility> <w:BreakWrappedTables/> <w:SnapToGridInCell/> <w:WrapTextWithPunct/> <w:UseAsianBreakRules/> <w:DontGrowAutofit/> </w:Compatibility> <w:BrowserLevel>MicrosoftInternetExplorer4</w:BrowserLevel> </w:WordDocument> </xml><![endif]--><!--[if gte mso 9]><xml> <w:LatentStyles DefLockedState="false" LatentStyleCount="156"> </w:LatentStyles> </xml><![endif]--><style> <!-- /* Style Definitions */ p.MsoNormal, li.MsoNormal, div.MsoNormal {mso-style-parent:""; margin:0in; margin-bottom:.0001pt; mso-pagination:widow-orphan; font-size:12.0pt; font-family:"Times New Roman"; mso-fareast-font-family:"Times New Roman";} @page Section1 {size:8.5in 11.0in; margin:1.0in 1.25in 1.0in 1.25in; mso-header-margin:.5in; mso-footer-margin:.5in; mso-paper-source:0;} div.Section1 {page:Section1;} --> </style><!--[if gte mso 10]> <style> /* Style Definitions */ table.MsoNormalTable {mso-style-name:"Table Normal"; mso-tstyle-rowband-size:0; mso-tstyle-colband-size:0; mso-style-noshow:yes; mso-style-parent:""; mso-padding-alt:0in 5.4pt 0in 5.4pt; mso-para-margin:0in; mso-para-margin-bottom:.0001pt; mso-pagination:widow-orphan; font-size:10.0pt; font-family:"Times New Roman"; mso-ansi-language:#0400; mso-fareast-language:#0400; mso-bidi-language:#0400;} </style> <![endif]--> <meta http-equiv="Content-Type" content="text/html; charset=utf-8"><meta name="ProgId" content="Word.Document"><meta name="Generator" content="Microsoft Word 11"><meta name="Originator" content="Microsoft Word 11"><link rel="File-List" href="file:///C:%5CDOCUME%7E1%5CSiah%5CLOCALS%7E1%5CTemp%5Cmsohtml1%5C01%5Cclip_filelist.xml"><!--[if gte mso 9]><xml> <w:WordDocument> <w:View>Normal</w:View> <w:Zoom>0</w:Zoom> <w:PunctuationKerning/> <w:ValidateAgainstSchemas/> <w:SaveIfXMLInvalid>false</w:SaveIfXMLInvalid> <w:IgnoreMixedContent>false</w:IgnoreMixedContent> <w:AlwaysShowPlaceholderText>false</w:AlwaysShowPlaceholderText> <w:Compatibility> <w:BreakWrappedTables/> <w:SnapToGridInCell/> <w:WrapTextWithPunct/> <w:UseAsianBreakRules/> <w:DontGrowAutofit/> </w:Compatibility> <w:BrowserLevel>MicrosoftInternetExplorer4</w:BrowserLevel> </w:WordDocument> </xml><![endif]--><!--[if gte mso 9]><xml> <w:LatentStyles DefLockedState="false" LatentStyleCount="156"> </w:LatentStyles> </xml><![endif]--><style> <!-- /* Style Definitions */ p.MsoNormal, li.MsoNormal, div.MsoNormal {mso-style-parent:""; margin:0in; margin-bottom:.0001pt; mso-pagination:widow-orphan; font-size:12.0pt; font-family:"Times New Roman"; mso-fareast-font-family:"Times New Roman";} @page Section1 {size:8.5in 11.0in; margin:1.0in 1.25in 1.0in 1.25in; mso-header-margin:.5in; mso-footer-margin:.5in; mso-paper-source:0;} div.Section1 {page:Section1;} --> </style><!--[if gte mso 10]> <style> /* Style Definitions */ table.MsoNormalTable {mso-style-name:"Table Normal"; mso-tstyle-rowband-size:0; mso-tstyle-colband-size:0; mso-style-noshow:yes; mso-style-parent:""; mso-padding-alt:0in 5.4pt 0in 5.4pt; mso-para-margin:0in; mso-para-margin-bottom:.0001pt; mso-pagination:widow-orphan; font-size:10.0pt; font-family:"Times New Roman"; mso-ansi-language:#0400; mso-fareast-language:#0400; mso-bidi-language:#0400;} </style> <![endif]-->
I was trying to mix two formulas in this one, but that wasnt working either.

<meta http-equiv="Content-Type" content="text/html; charset=utf-8"><meta name="ProgId" content="Word.Document"><meta name="Generator" content="Microsoft Word 11"><meta name="Originator" content="Microsoft Word 11"><link rel="File-List" href="file:///C:%5CDOCUME%7E1%5CSiah%5CLOCALS%7E1%5CTemp%5Cmsohtml1%5C01%5Cclip_filelist.xml"><!--[if gte mso 9]><xml> <w:WordDocument> <w:View>Normal</w:View> <w:Zoom>0</w:Zoom> <w:PunctuationKerning/> <w:ValidateAgainstSchemas/> <w:SaveIfXMLInvalid>false</w:SaveIfXMLInvalid> <w:IgnoreMixedContent>false</w:IgnoreMixedContent> <w:AlwaysShowPlaceholderText>false</w:AlwaysShowPlaceholderText> <w:Compatibility> <w:BreakWrappedTables/> <w:SnapToGridInCell/> <w:WrapTextWithPunct/> <w:UseAsianBreakRules/> <w:DontGrowAutofit/> </w:Compatibility> <w:BrowserLevel>MicrosoftInternetExplorer4</w:BrowserLevel> </w:WordDocument> </xml><![endif]--><!--[if gte mso 9]><xml> <w:LatentStyles DefLockedState="false" LatentStyleCount="156"> </w:LatentStyles> </xml><![endif]--><style> <!-- /* Style Definitions */ p.MsoNormal, li.MsoNormal, div.MsoNormal {mso-style-parent:""; margin:0in; margin-bottom:.0001pt; mso-pagination:widow-orphan; font-size:12.0pt; font-family:"Times New Roman"; mso-fareast-font-family:"Times New Roman";} @page Section1 {size:8.5in 11.0in; margin:1.0in 1.25in 1.0in 1.25in; mso-header-margin:.5in; mso-footer-margin:.5in; mso-paper-source:0;} div.Section1 {page:Section1;} --> </style><!--[if gte mso 10]> <style> /* Style Definitions */ table.MsoNormalTable {mso-style-name:"Table Normal"; mso-tstyle-rowband-size:0; mso-tstyle-colband-size:0; mso-style-noshow:yes; mso-style-parent:""; mso-padding-alt:0in 5.4pt 0in 5.4pt; mso-para-margin:0in; mso-para-margin-bottom:.0001pt; mso-pagination:widow-orphan; font-size:10.0pt; font-family:"Times New Roman"; mso-ansi-language:#0400; mso-fareast-language:#0400; mso-bidi-language:#0400;} </style> <![endif]--> <meta http-equiv="Content-Type" content="text/html; charset=utf-8"><meta name="ProgId" content="Word.Document"><meta name="Generator" content="Microsoft Word 11"><meta name="Originator" content="Microsoft Word 11"><link rel="File-List" href="file:///C:%5CDOCUME%7E1%5CSiah%5CLOCALS%7E1%5CTemp%5Cmsohtml1%5C01%5Cclip_filelist.xml"><!--[if gte mso 9]><xml> <w:WordDocument> <w:View>Normal</w:View> <w:Zoom>0</w:Zoom> <w:PunctuationKerning/> <w:ValidateAgainstSchemas/> <w:SaveIfXMLInvalid>false</w:SaveIfXMLInvalid> <w:IgnoreMixedContent>false</w:IgnoreMixedContent> <w:AlwaysShowPlaceholderText>false</w:AlwaysShowPlaceholderText> <w:Compatibility> <w:BreakWrappedTables/> <w:SnapToGridInCell/> <w:WrapTextWithPunct/> <w:UseAsianBreakRules/> <w:DontGrowAutofit/> </w:Compatibility> <w:BrowserLevel>MicrosoftInternetExplorer4</w:BrowserLevel> </w:WordDocument> </xml><![endif]--><!--[if gte mso 9]><xml> <w:LatentStyles DefLockedState="false" LatentStyleCount="156"> </w:LatentStyles> </xml><![endif]--><style> <!-- /* Style Definitions */ p.MsoNormal, li.MsoNormal, div.MsoNormal {mso-style-parent:""; margin:0in; margin-bottom:.0001pt; mso-pagination:widow-orphan; font-size:12.0pt; font-family:"Times New Roman"; mso-fareast-font-family:"Times New Roman";} @page Section1 {size:8.5in 11.0in; margin:1.0in 1.25in 1.0in 1.25in; mso-header-margin:.5in; mso-footer-margin:.5in; mso-paper-source:0;} div.Section1 {page:Section1;} --> </style><!--[if gte mso 10]> <style> /* Style Definitions */ table.MsoNormalTable {mso-style-name:"Table Normal"; mso-tstyle-rowband-size:0; mso-tstyle-colband-size:0; mso-style-noshow:yes; mso-style-parent:""; mso-padding-alt:0in 5.4pt 0in 5.4pt; mso-para-margin:0in; mso-para-margin-bottom:.0001pt; mso-pagination:widow-orphan; font-size:10.0pt; font-family:"Times New Roman"; mso-ansi-language:#0400; mso-fareast-language:#0400; mso-bidi-language:#0400;} </style> <![endif]--> =SUMIF('Credit Card'!D70:D92,'Expense Data'!A4:A27)-(SUM('Credit Card'!C70:C92))


This one comes really close to working...but it makes you put that ) after the 'Expense Data'?A4:A27 and if you watch the formula when you type that then it takes away the sum range option of the SUMIF Formula and starts on the SUM formula, and doesnt go back to it.


I hope this makes sense, and I know there has to be a way to do this. PLEASE HELP!!!

 

[gnrboyd]

i'm not 100% sure I understand what you are trying to accomplish but I think this is what you are looking for:

=SUMIF('Credit Card'!D70:D92,'Expense Data'!D4,'Credit Card'!C70:C92)-'Credit Card'!C71

 

[barry houdini]

Try using SUMPRODUCT like

=SUMPRODUCT(--(CriteriaRange=Criterion),RangeToAdd-RangetoSubtract)

or 2 SUMIFS

=SUMIF(CriteriaRange,Criterion,RangeToAdd)-SUMIF(CriteriaRange,Criterion,RangetoSubtract)

 

[siasarma]

Sorry, I wasn't very clear on that. Thats not quite what I need. Let me break my formula down.
RANGE CRITERIA
<meta http-equiv="Content-Type" content="text/html; charset=utf-8"><meta name="ProgId" content="Word.Document"><meta name="Generator" content="Microsoft Word 11"><meta name="Originator" content="Microsoft Word 11"><link rel="File-List" href="file:///C:%5CDOCUME%7E1%5CSiah%5CLOCALS%7E1%5CTemp%5Cmsohtml1%5C01%5Cclip_filelist.xml"><!--[if gte mso 9]><xml> <w:WordDocument> <w:View>Normal</w:View> <w:Zoom>0</w:Zoom> <w:PunctuationKerning/> <w:ValidateAgainstSchemas/> <w:SaveIfXMLInvalid>false</w:SaveIfXMLInvalid> <w:IgnoreMixedContent>false</w:IgnoreMixedContent> <w:AlwaysShowPlaceholderText>false</w:AlwaysShowPlaceholderText> <w:Compatibility> <w:BreakWrappedTables/> <w:SnapToGridInCell/> <w:WrapTextWithPunct/> <w:UseAsianBreakRules/> <w:DontGrowAutofit/> </w:Compatibility> <w:BrowserLevel>MicrosoftInternetExplorer4</w:BrowserLevel> </w:WordDocument> </xml><![endif]--><!--[if gte mso 9]><xml> <w:LatentStyles DefLockedState="false" LatentStyleCount="156"> </w:LatentStyles> </xml><![endif]--><style> <!-- /* Style Definitions */ p.MsoNormal, li.MsoNormal, div.MsoNormal {mso-style-parent:""; margin:0in; margin-bottom:.0001pt; mso-pagination:widow-orphan; font-size:12.0pt; font-family:"Times New Roman"; mso-fareast-font-family:"Times New Roman";} @page Section1 {size:8.5in 11.0in; margin:1.0in 1.25in 1.0in 1.25in; mso-header-margin:.5in; mso-footer-margin:.5in; mso-paper-source:0;} div.Section1 {page:Section1;} --> </style><!--[if gte mso 10]> <style> /* Style Definitions */ table.MsoNormalTable {mso-style-name:"Table Normal"; mso-tstyle-rowband-size:0; mso-tstyle-colband-size:0; mso-style-noshow:yes; mso-style-parent:""; mso-padding-alt:0in 5.4pt 0in 5.4pt; mso-para-margin:0in; mso-para-margin-bottom:.0001pt; mso-pagination:widow-orphan; font-size:10.0pt; font-family:"Times New Roman"; mso-ansi-language:#0400; mso-fareast-language:#0400; mso-bidi-language:#0400;} </style> <![endif]--> =SUMIF('Credit Card'!D70:D92,'Expense Data'!D4, THEN I want this whole sum
SUM_RANGE

range part to subtract. 'Credit Card'!C70:C92)


Hope this makes more sense!!!

 

[gnrboyd]

If one of Barry's suggestions doesn't work, you might want to post some sample data so we can better understand what you are trying to do.

 

[siasarma]

I cant use the SUMPRODUCT formula cuz it really has to be the SUMIF formula. Mixing 2 SUMIFS together wont work because I dont need 2 mixed. I just need to figure out how to make the SUMRANGE part subtract instead of add. I will try to post my data, but this will get really confusing as it is spread out over many sheets, and this is awfully confusing what I am trying to do. But here goes:

I am basically making a credit card log where I type in the transaction and an account code, and it counts how many times I type in an Account and adds the amount spent on that account in another sheet, so that I can keep track of how much money was spent at each account. I have that working ok but then I wanted to do credits. I wanted to type in a positive number and have it subtract that number from the correct account. For instance you buy some groceries from walmart for $5. Then you return those groceries, and you have the $5 put back on your credit card, but you need your Groceries Account to subtract the $5. Here's how my setup works.

I type in my transaction and account in my credit card log:
<TABLE style="WIDTH: 355pt; BORDER-COLLAPSE: collapse" cellSpacing=0 cellPadding=0 width=472 border=0 x:str><COLGROUP><COL style="WIDTH: 114pt; mso-width-source: userset; mso-width-alt: 5558" width=152><COL style="WIDTH: 62pt; mso-width-source: userset; mso-width-alt: 2998" width=82><COL style="WIDTH: 72pt; mso-width-source: userset; mso-width-alt: 3510" width=96><COL style="WIDTH: 107pt; mso-width-source: userset; mso-width-alt: 5193" width=142><TBODY><TR style="HEIGHT: 15pt; mso-height-source: userset" height=20><TD class=xl28 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext 1pt solid; BORDER-LEFT: #ece9d8; WIDTH: 114pt; BORDER-BOTTOM: windowtext 1pt solid; HEIGHT: 15pt; BACKGROUND-COLOR: #ffcc99" width=152 height=20>Transaction</TD><TD class=xl28 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext 1pt solid; BORDER-LEFT: #ece9d8; WIDTH: 62pt; BORDER-BOTTOM: windowtext 1pt solid; BACKGROUND-COLOR: #ffcc99" width=82>Date</TD><TD class=xl28 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext 1pt solid; BORDER-LEFT: #ece9d8; WIDTH: 72pt; BORDER-BOTTOM: windowtext 1pt solid; BACKGROUND-COLOR: #ffcc99" width=96>Amount</TD><TD class=xl29 style="BORDER-RIGHT: windowtext 1pt solid; BORDER-TOP: windowtext 1pt solid; BORDER-LEFT: #ece9d8; WIDTH: 107pt; BORDER-BOTTOM: windowtext 1pt solid; BACKGROUND-COLOR: #ffcc99" width=142>Account</TD></TR><TR style="HEIGHT: 15pt; mso-height-source: userset" height=20><TD class=xl24 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 15pt; BACKGROUND-COLOR: #ffff99" height=20> </TD><TD class=xl26 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: #ffff99"> </TD><TD class=xl27 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: #ffff99" align=right x:num="5">$5.00</TD><TD class=xl25 style="BORDER-RIGHT: windowtext 1pt solid; BORDER-TOP: windowtext; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: #ffff99"> Groceries</TD></TR></TBODY></TABLE>

I have a main list of accounts on my summary page:
<TABLE style="WIDTH: 119pt; BORDER-COLLAPSE: collapse" cellSpacing=0 cellPadding=0 width=159 border=0 x:str><COLGROUP><COL style="WIDTH: 119pt; mso-width-source: userset; mso-width-alt: 5814" width=159><TBODY><TR style="HEIGHT: 48pt; mso-height-source: userset" height=64><TD class=xl26 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; WIDTH: 119pt; BORDER-BOTTOM: windowtext 1pt solid; HEIGHT: 48pt; BACKGROUND-COLOR: #ccffff" width=159 height=64>Expense Accounts</TD></TR><TR style="HEIGHT: 18pt; mso-height-source: userset" height=24><TD class=xl24 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: windowtext; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 18pt; BACKGROUND-COLOR: #ccffff" height=24>A test</TD></TR><TR style="HEIGHT: 18pt; mso-height-source: userset" height=24><TD class=xl25 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: windowtext; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 18pt; BACKGROUND-COLOR: #ccffff" height=24>Car Maintenance</TD></TR><TR style="HEIGHT: 18pt; mso-height-source: userset" height=24><TD class=xl25 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: windowtext; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 18pt; BACKGROUND-COLOR: #ccffff" height=24>Cash</TD></TR><TR style="HEIGHT: 18pt; mso-height-source: userset" height=24><TD class=xl25 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: windowtext; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 18pt; BACKGROUND-COLOR: #ccffff" height=24>Electric</TD></TR><TR style="HEIGHT: 18pt; mso-height-source: userset" height=24><TD class=xl25 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: windowtext; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 18pt; BACKGROUND-COLOR: #ccffff" height=24>Entertainment</TD></TR><TR style="HEIGHT: 18pt; mso-height-source: userset" height=24><TD class=xl25 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: windowtext; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 18pt; BACKGROUND-COLOR: #ccffff" height=24>Gifts</TD></TR></TBODY></TABLE>
So this is the main one where you can add more to the list or sort it in alphabetical order.

Then I have a page called Expense Data where it holds all the accounts and totals for how much was spent on each account.

<TABLE style="WIDTH: 160pt; BORDER-COLLAPSE: collapse" cellSpacing=0 cellPadding=0 width=213 border=0 x:str><COLGROUP><COL style="WIDTH: 93pt; mso-width-source: userset; mso-width-alt: 4534" width=124><COL style="WIDTH: 67pt; mso-width-source: userset; mso-width-alt: 3254" width=89><TBODY><TR style="HEIGHT: 12.75pt" height=17><TD class=xl24 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext 1pt solid; BORDER-LEFT: #ece9d8; WIDTH: 93pt; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" width=124 height=17>Account</TD><TD class=xl25 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: windowtext 1pt solid; BORDER-LEFT: #ece9d8; WIDTH: 67pt; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent" width=89>Total</TD></TR><TR style="HEIGHT: 14.25pt; mso-height-source: userset" height=19><TD class=xl26 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 14.25pt; BACKGROUND-COLOR: transparent" height=19>A test</TD><TD class=xl27 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: windowtext; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent" align=right x:num="0">$0.00</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl28 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext 0.5pt solid; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" height=17>Car Maintenance</TD><TD class=xl27 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: windowtext; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent" align=right x:num="0">$0.00</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl26 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" height=17>Cash</TD><TD class=xl27 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: windowtext; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent" align=right x:num="0">$0.00</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl26 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" height=17>Electric</TD><TD class=xl27 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: windowtext; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent" align=right x:num="0">$0.00</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl26 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" height=17>Entertainment</TD><TD class=xl27 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: windowtext; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent" align=right x:num="0">$0.00</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl26 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" height=17>Gifts</TD><TD class=xl27 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: windowtext; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent" align=right x:num="0">$0.00</TD></TR></TBODY></TABLE>



Now the Accounts on the Expense Data one is linked to the main one, so if the main ones changes this one will update with it. That part is no problem. The totals are being linked to the Credit Card log. I need to have the totals stay with the correct Account if the main page is changed. Hence my SUMIF Formula. So in my TOTAL formula I am trying to enter in the SUMIF formula. SUMIF( (RANGE) meaning the ACCOUNTS on the CREDIT CARD TRANSACTION PAGE, EQUAL THE (CRITERIA) Which is the Accounts on the Expense Data Sheet, ( so they stay alphabetized with the main list) THEN SUBTRACT (SUMRANGE) ALL THE AMOUNT CELLS IN THE CREDIT CARD AMOUNT CELLS. And display the total of those in the Expense Data Total Cell. That way they all stay alphabetical with the main list, and if it changes the total will stay with the correct account instead of just staying in that cell.

I can get it to work fine with the same SUMIF formula, ADDING the Amount cells, I just cant get it to SUBTRACT those cells.

I cant thank you guys enough for your time on this! Sorry this is such a mess! I just think that if I can get this to subtract the range of cells in the SUMRANGE instead of add I would be just fine!!

 

[siasarma]

Well, I found a way that will work....there's got to be an easier way to do it, but this is how I am doing it.

=SUMIF('Credit Card'!D70:D92,'Expense Data'!A31,'Credit Card'!C70)-'Credit Card'!C70-'Credit Card'!C70-'Credit Card'!C71-'Credit Card'!C72-'Credit Card'!C73

And so forth and so forth. If anyone comes up with a better idea please let me know!!!

Thanks!!!

 

[gnrboyd]

I've read and re-read your post a few times and I'm still having difficulty figuring out exactly what you're trying to do. If you'd like, you can send the file to me and I will take a look at it. (I'm using Excel 2002) I know I can't open the 2007 version but not sure about anything in between. If that is the case, you can always save a copy in the older version. If you'd like me to look at it, send me a PM and I'll give you an email address to send the file to.

 

[gnrboyd]

I looked at this again and I think this may be what you are trying to do. If not, read my previous post and we can go from there.<SCRIPT language=JavaScript src="http://www.interq.or.jp/sun/puremis...[/XD][XH][/XH][/XR][/RANGE][/XH][/XR][/RANGE]

 

[siasarma]

Yeah, that looks like it would have worked too. Instead of putting the word "return" in the formula, I would have had to select the cell because the word "return" would change if the list was sorted in alphabetical order like I have it set up. Anyways...it was very confusing to try to explain...but I have a formula that will work for me, so THANKS FOR YOUR HELP!!!!