SUMIFS question or a betterway

[sdinyer]

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> </o>
Right now I am using Excel 2008 for MAC Version 12.2.6. Mac Os X 10.6.4
<o> </o>
Here’s my problem.
<o> </o>
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> </o>
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> </o>
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> </o>
=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> </o>
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> </o>
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> </o>
=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> </o>
I can type in any name of any project in C4 of “MoneyFlow” and everything recalculates. Here’s the problem,,,
<o> </o>
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> </o>
Any Ideas?


Thank you for being here.
Steve

<!--EndFragment-->

 

[Aladin Akyurek]

Try...

=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]

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]

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]

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:

=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]

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]

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:

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

 

[sdinyer]

Thanks, I'll try this. Can't now.
Steve

 

[T. Valko]

.

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]

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

=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)))