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

 

[T. Valko]

Vagaries of copy-and-paste...

 

[T. Valko]

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

WTH!

Well, it was the correct syntax 'til the forum software "chewed it up"!

One of these days I'll figure out how this forum works! Or not!

 

[sdinyer]

Yes, Yes, Yes, Yes, Yes! Aladin Akyurek. This seems to work great. I tried it with the first of the series of cells and it worked perfectly. I'm about to move it to other cells to make sure it works for everything all around, but I don't see why it shouldn't. I keep getting interrupted. So it might take a while before I know for sure but I'm sure it will. I can't Thank You enough. If you don't hear from me,,, it works..

T. Valko. I didn't get to try your solution, yet. It looks interesting but I need to really study it in order to understand what is going on. I thank you, too, for you efforts and I will play with it and use it to expand my knowledge.

Thanks to every one for all your help.
Steve

 

[Aladin Akyurek]

Yes, Yes, Yes, Yes, Yes! Aladin Akyurek. This seems to work great. I tried it with the first of the series of cells and it worked perfectly. I'm about to move it to other cells to make sure it works for everything all around, but I don't see why it shouldn't. I keep getting interrupted. So it might take a while before I know for sure but I'm sure it will. I can't Thank You enough. If you don't hear from me,,, it works..

T. Valko. I didn't get to try your solution, yet. It looks interesting but I need to really study it in order to understand what is going on. I thank you, too, for you efforts and I will play with it and use it to expand my knowledge.

Thanks to every one for all your help.
Steve

Great. Thanks for the feedback.

 

[T. Valko]

T. Valko. I didn't get to try your solution, yet. It looks interesting but I need to really study it in order to understand what is going on. I thank you, too, for you efforts and I will play with it and use it to expand my knowledge.

Thanks to every one for all your help.
Steve

The formula I posted is the corrected syntax version of the formula posted by Aladin.