I have a private budget where I list all my bank transaction like

 Date        Type         Amount
 1. May 02   Car          $ 95,5
 1. May 02   House        $ 10
 9. May 02   Car          $ 10

Currently I make a summary by using =SUMIF but I would also like to
be able to combine this with a period like 1. May 02 - 31. May 02.

I need to know how to summarize transactions from a specificed period and category.

Like I wanted to know the total sum of the type: House for the period: 1/5-31/5.

Hello,

Since Excel stores dates in terms of # of days from 1/1/1900 until the
date, you need to first convert the date into a number format to build
your formula. Use the =DATE(year,month,day) command to find the # of
days for your ranges.

i.e. =date(2002,5,1) = 37377
i.e. =date(2002,5,31) = 37407
note: make sure the cells you use to convert are "General" or "Number"
or you will just see the date you build (like 1/5/2002 and 31/5/2002
instead of the numbers 37377 and 37407)

Once you have the ranges of the dates you want to search for, it's
just a compound =sumif command to do the rest. I'm assuming all of
your dates are in column A and all of your amounts are in column C. If
this is the case use this formula:

=SUMIF(A:A,">=37377",B:B)-SUMIF(A:A,">37407",C:C)

This formula will add all the amounts including and after 1/5/2002 and
then subtract from that all the amounts after 31/5/2002. This will
leave you with the amounts from 1/5/2002 until 31/5/2002 (it helps if
you draw a small venn diagram to visualize this).


Additional Links:

Help with sumif for two criteria:
http://contextures.com/xlfaqFun.html#Fun12


Search Strategy:

sumif "two criteria" on google:
://www.google.com/search?q=sumif+%22two+criteria%22


Thank you for the opportunity to answer your question, if you require
more information, please clarify the question, or if you find this
answer satisfactory, please feel free to rate it. Thank you!

skermit-ga

---

Clarification of Answer by skermit-ga on 15 Jun 2002 08:58 PDT

Arrgh, I just read the part about the category... I'm working on a
clarification right now, shouldn't take too much longer to add it into
the current formula.

---

Request for Answer Clarification by bluee-ga on 15 Jun 2002 09:11 PDT

I would then have to convert all my existing dates into unix days. Is
there an automatic way of doing this?

---

Clarification of Answer by skermit-ga on 15 Jun 2002 09:46 PDT

Here is the updated formula which I will explain:

=SUM(IF(B1:B9999="House",C1:C9999) * IF(A1:A9999>=37377,1, 0)) -
SUM(IF(B1:B9999="House",C1:C9999) * IF(A1:A9999>37407,1, 0))

It's just a modified version of the formula listed above, but is an
array formula meaning once you type this in instead of hitting enter,
YOU MUST HIT CONTROL SHIFT ENTER so that Microsoft treats it as an
array formula. What it does is just passes each cell through a test
comparison to a string and assigns a multiplier to it (in this case 1
* the amount or 0 * the amount if it's not the correct select type).
Again, I'm assuming your dates are in column A, budget type is column
B, and amount is column C.

As per your clarification request, you do not need to change the dates
of each cell, if you made them date formatted cells. Excel will
automatically read them as days since 1/1/1900 automatically. What you
do need to do in a temporary cell is use the =date() function to find
out the correct boundaries for your formula to search through.

i.e. if you wanted to search from 3/2/2002 - 31/5/2002 for House
payments you would use the formula:

=SUM(IF(B1:B9999="House",C1:C9999) * IF(A1:A9999>=37290,1, 0)) -
SUM(IF(B1:B9999="House",C1:C9999) * IF(A1:A9999>37407,1, 0))
note: 37290 is 3/2/2002 in number of days since 1/1/1900 format

and then make sure you push CONTROL SHIFT ENTER when you're entering
the formula in, not enter by itself.

skermit-ga

---

Clarification of Answer by skermit-ga on 15 Jun 2002 10:10 PDT

I uploaded a sample file so you can see exactly what I mean, with some
comments here:

http://makeashorterlink.com/?I2FE62011

it has been checked for macro viruses and such, and is clean so don't
worry. Please rate my answer if you feel it is sufficient, or clarify
for more information. Thank you again.

skermit-ga

Thanks for your help! I got it to work using the example you have attached.

1. Type your dates as eg 5 May 02 (leave out the dot).  
   Excel will convert them to its internal date format 
   automatically.
2. There are many ways to compute the sum.  I prefer to
   create a new colum that contains a 1 if a row matches
   criteria, eg consider the following spreadsheet which
   should be entered in the top left corner (starting at
   cell A1):

   A		B	C	D		
1: Date		Type	Amount	Matches
2: 01-May-02	Car	$95.50 	0
3: 01-May-02	House	$10.00 	1
4: 05-May-02	House	$5.34 	1
5: 09-May-02	Car	$10.00 	0
6: 10-May-02	House	$10.00 	0
7: 
8: Category:	House		
8: Date range:	01-May	07-May	
9: 
10: Sum:		15.34	

The "sorted" column in for example row
two contains the formula
  =AND(B2=$B$8, A2>=$B$9, A2<=$C$9)*1
where the first part of the AND checks
if the category column (B2) matches
the desired category ($b$8).  The 
second and third parts of the AND check
if the date is in the desired range.
The "*1" is needed to convert from a 
boolean value (TRUE/FALSE) to a 1 or 
a 0.  One could also have used eg
IF(AND(...),1,0).  Copy the formula
in the Match column to all the rows
containing the data you want to filter
and sum.

The sum is computed using
=SUMPRODUCT(C2:C6,D2:D6)
which multiplies the amount colum (C)
with the ones / zeros column.

Another approach is to enter for example
=IF(AND(B2=$B$8, A2>=$B$9, A2<=$C$9),C2,0)
in a column - this will give you the amounts
if they match, else zeros.  Then the sum
is computed using simply =SUM(D2:D6) 
(assuming you entered the IF formulas in 
the D column).  You could also use a nested
IF instead of the AND.

Thanks jheti-ga, but that's exactly what I did in my answer, if you
notice, there's a "match" array generated with a 1 multipllier if
found and 0 multiplier if not found, effectively summing only the
terms you needed. Thank you for your effort though!

skermit-ga

1. I normally do not work with the integer format dates, 
   as Excel can compare dates displayed and entered 
   in the formats humans more often use (e.g. Day Month 
   Year).  I guess it is a matter of personal preference -
   skermit's approach should also work.
2. In the previous comment, I tried to align columns 
   using Tab characters, which is apparently not 
   something Google Answers supports.  Here is an 
   example spreadsheet formatted using spaces, this 
   time using the =IF(...) and SUM method.  Hopefully 
   the following explanation will be clearer.

   A             B      C       D
1: Date          Type   Amount  Matching
2: 01-May-02     Car    $95.50     $0.00 
3: 01-May-02     House  $10.00    $10.00 
4: 05-May-02     House   $5.34     $5.34 
5: 09-May-02     Car    $10.00     $0.00 
6: 10-May-02     House  $10.00     $0.00 
7: Total:                        $15.34 
8: for category: House		
9: for dates:    01-May 07-May	

The Matching column contains the amount
if the row matches the criteria, else
zero - this is computed using:
=IF(AND(B2=$B$8, A2>=$B$9, A2<=$C$9),C2,0)
for row two - copy this to rows 3 to 6 -
Excel will automatically adjust the 
formula to refer to the other rows' cells.
In the formula, $B$8 is the desired category, 
$B$9 is the first date, and $C$9
is the last date (entered after
"for category:" and "for dates:").  (The "$"
signs are used to ensure that the reference
is an absolute reference - you do not want
Excel to change those when copying the cell.)

The sum is simply computed using =SUM(D2:D6) 
when using this method.

(In the previous explanation, read 
'"Matching" column' for '"sorted" 
column' and 'Match column' - sorry
about the confusion.)

I responded after reading "skermit's" 
original answer - by the time I posted
the suggested answer had been amended.

It seems that we are converging on 
a suggested set of solutions.

Good to see great minds think alike! Thanks again jheti-ga.

skermit-ga

Hi jheti-ga,

Somehow I was unable to rate your answer. But thanks for your answer aswell.

:-)