SUMIFS question or a betterway

sdinyer

New Member
Joined
Mar 21, 2011
Messages
10
I’m a new guy .
I like to use spreadsheets as a tool but I’m only fair at programming. I can usually struggle through semi-complicated programming by reading, copying other formula, and brute force. I am new to this list and I will try not to abuse it. I can usually figure things out. I have been trying to figure out a few things for a week now. Can’t quite figure this one out.
<o:p> </o:p>
Right now I am using Excel 2008 for MAC Version 12.2.6. Mac Os X 10.6.4
<o:p> </o:p>
Here’s my problem.
<o:p> </o:p>
I have a 2sheet workbook. The first sheet (Entries from 10-6-09) has most of the data. The second sheet (MoneyFlow) has Cells separating the data into totals for checks and deposits by every month of various years for the whole list.
<o:p> </o:p>
On the main Sheet, each row of entry whether it is a check or a deposit has a “Tag” column, showing what project the charge belongs to.
<o:p> </o:p>
This formula below works perfectly. It goes to the “date” column (B23:B252), tests to see which dates occur in one month. (as a note I just realized that I should use => and <= I’ll fix that ) then it SUMS (D23:D252) which is the “expense” column.
<o:p> </o:p>
=SUMIFS('Entries from 10-6-09'!D23:D252,'Entries from 10-6-09'!B23:B252,">"&DATE(2009,11,1),'Entries from 10-6-09'!B23:B252,"<"&DATE(2009,11,30))
<o:p> </o:p>
Great so far. I know I could slim the formula down a little but I’m an amateur. I’ll ease in to that.
<o:p> </o:p>
Then, I wanted to have the ability to sum by whatever project “Tag”, I want. I don’t want separate data for each project. I want to type into a Cell which project I want to see and everything recalculates for that entry. I use the below formula, which is the above formula with ad added line at the end and it works perfectly!
<o:p> </o:p>
=SUMIFS('Entries from 10-6-09'!D23:D252,'Entries from 10-6-09'!B23:B252,">"&DATE(2009,10,1),'Entries from 10-6-09'!B23:B252,"<"&DATE(2009,10,31),'Entries from 10-6-09'!F23:F252,MoneyFlow!C4)
<o:p> </o:p>
I can type in any name of any project in C4 of “MoneyFlow” and everything recalculates. Here’s the problem,,,
<o:p> </o:p>
I can’t for the life of me figure out how to, now, get it to ignore C4, if I want, to again total everything in that month (without a Project tag). Even if I leave C4 blank(which makes sense) I just get “0.00”. I’ve tried various things, like typing all the “Tags” in C4 with/without various modifyers. I tried additions to the formula, like “if”, “not”, “or” , “+” and many other things. Excel complains.
<o:p> </o:p>
Any Ideas?


Thank you for being here.
Steve

<!--EndFragment-->
 

Some videos you may like

Excel Facts

What is the shortcut key for Format Selection?
Ctrl+1 (the number one) will open the Format dialog for whatever is selected.

Aladin Akyurek

MrExcel MVP
Joined
Feb 14, 2002
Messages
85,201
Try...
Rich (BB code):
=SUMPRODUCT(SUMIFS(
   'Entries from 10-6-09'!D23:D252,
   'Entries from 10-6-09'!B23:B252,">"&DATE(2009,10,1),
   'Entries from 10-6-09'!B23:B252,"<"&DATE(2009,10,31),
   'Entries from 10-6-09'!F23:F252,
     IF(MoneyFlow!C4="",'Entries from 10-6-09'!F23:F252,MoneyFlow!C4)))
 

sdinyer

New Member
Joined
Mar 21, 2011
Messages
10
Thank you for the very speedy reply.

The addition you gave me to the formula does allow a sum when C4 is empty but the calculation is completely wrong. way wrong.

I can't even figure out where the calculation comes from. The number would be $1256.88 if it followed the dates. The sum given is $188,532.00. That is far more than everything in the columns D23:D252 which would be $47,291.49 if summed with no date restrictions. The front part of the formula still works as it did before.

Since you put it in a form that I might not have even thought of, I'm going to play with it more to see if I can get it to sum right.
Thanks again
Steve
 

AlanAnderson

Board Regular
Joined
Jun 7, 2010
Messages
134
Hi Steve,
I'm also pretty new to VBA.
I also had a situation where SUMIf would have been the perfect solution but IT DID NOT WORK.
Basically, as long as the cell containing the condition was numeric (eg >= 100 etc.) it worked well. The moment that cell was Text it worked but gave wrong results.
Whether this was my bad programming or an Excel fault I do not know.
In the end I worked around it using a "For" loop and then "if not ....then"
Hope this helps.

Alan
 

Aladin Akyurek

MrExcel MVP
Joined
Feb 14, 2002
Messages
85,201

ADVERTISEMENT

Thank you for the very speedy reply.

The addition you gave me to the formula does allow a sum when C4 is empty but the calculation is completely wrong. way wrong.

I can't even figure out where the calculation comes from. The number would be $1256.88 if it followed the dates. The sum given is $188,532.00. That is far more than everything in the columns D23:D252 which would be $47,291.49 if summed with no date restrictions. The front part of the formula still works as it did before.

Since you put it in a form that I might not have even thought of, I'm going to play with it more to see if I can get it to sum right.
Thanks again
Steve

Try the following instead:
Rich (BB code):
=SUMPRODUCT(
   'Entries from 10-6-09'!D23:D252,
   --('Entries from 10-6-09'!B23:B252,">"&DATE(2009,10,1)),
   --('Entries from 10-6-09'!B23:B252,"<"&DATE(2009,10,31)),
   --('Entries from 10-6-09'!F23:F252=
         IF(MoneyFlow!C4="",'Entries from 10-6-09'!F23:F252,MoneyFlow!C4)))
 

sdinyer

New Member
Joined
Mar 21, 2011
Messages
10
To AlanAnderson. Actually my formula is working pretty good with the text(up to the point where I want no text). I think I might be on the right track but I think I need another variable or logical statement or something. I want to try to tweak it first before I have to try to figure out a different way from scratch. I am unfamiliar with "for" and"loops" Besides, VBA won't run on my version of Mac Excel but that's another stupid story.
As to your problem did you try putting the text in quotes? i.e. "text" That works for me.

To Aladin Akyurek, The new formula you sugested gave an "error in formula" message. I'm looking to see if there might be some formatting thing wrong.
Thanks to both of you

 

AlanAnderson

Board Regular
Joined
Jun 7, 2010
Messages
134

ADVERTISEMENT

Hi Steve, Yes I did. Tried single "'s as well as double "" - made no difference. I had exact same issue as you are having - it was giving impossible answers.
The routine I eventually used was basically the following:
Code:
For x = 1 to FinalRow
if cells(X,1)= whatever then
vTotal = cells(g,2) + vTotal
End if
next x
Above code assumes criteria is in col A while amounts to be added are in column B.

I have no idea whether or not this will work with your Mac.

Regards,

Alan
 

T. Valko

Well-known Member
Joined
May 9, 2009
Messages
16,623
.

To Aladin Akyurek, The new formula you sugested gave an "error in formula" message. I'm looking to see if there might be some formatting thing wrong.

=SUMPRODUCT(
'Entries from 10-6-09'!D23:D252,
--('Entries from 10-6-09'!B23:B252,">"&DATE(2009,10,1)),
--('Entries from 10-6-09'!B23:B252,"<"&DATE(2009,10,31)),
--('Entries from 10-6-09'!F23:F252=
IF(MoneyFlow!C4="",'Entries from 10-6-09'!F23:F252,MoneyFlow!C4)))

Here's the correct syntax.

=SUMPRODUCT(
'Entries from 10-6-09'!D23:D252,
--('Entries from 10-6-09'!B23:B252>DATE(2009,10,1)),
--('Entries from 10-6-09'!B23:B252<DATE(2009,10,31)),< font>
--('Entries from 10-6-09'!F23:F252=
IF(MoneyFlow!C4="",'Entries from 10-6-09'!F23:F252,MoneyFlow!C4)))

I don't know if that does what you want, though!


If I were you I'd use cells to hold the date criteria.
  • A1 = 10/1/2009
  • B1 = 10/31/2009
Let's get rid of all those line feeds...

=SUMPRODUCT('Entries from 10-6-09'!D23:D252,--('Entries from 10-6-09'!B23:B252 > A1),--('Entries from 10-6-09'!B23:B252 < B1),--('Entries from 10-6-09'!F23:F252=IF(MoneyFlow!C4="",'Entries from 10-6-09'!F23:F252,MoneyFlow!C4)))<B1),--('ENTRIES font 10-6-09?!F23:F252,MoneyFlow!C4)))< from 10-6-09?!F23:F252="IF(MoneyFlow!C4="",'Entries">
 

Aladin Akyurek

MrExcel MVP
Joined
Feb 14, 2002
Messages
85,201
...To Aladin Akyurek, The new formula you sugested gave an "error in formula" message. I'm looking to see if there might be some formatting thing wrong.
...

Vagaries of copy-and-paste... Now it should be ok...
Rich (BB code):
=SUMPRODUCT(
  'Entries from 10-6-09'!D23:D252,
  --('Entries from 10-6-09'!B23:B252 > DATE(2009,10,1)),
  --('Entries from 10-6-09'!B23:B252 < DATE(2009,10,31)),
  --('Entries from 10-6-09'!F23:F252 =
      IF(MoneyFlow!C4="",'Entries from 10-6-09'!F23:F252,MoneyFlow!C4)))
 

Watch MrExcel Video

Forum statistics

Threads
1,122,518
Messages
5,596,622
Members
414,082
Latest member
sasmita

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.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

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

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

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
Top