I'm trying to figure out a way to Sum charges based upon date criteria and charge type criteria. Here's my problem.

I keep a spreadsheet for personal finances. I use codes for different charges to easily sum and keep track of categories. Here's what it would look like:

Date Description Amount Code
1/15/10 Groceries $50.00 GROC
1/21/10 Gas $22.50 GAS


At the end of each month, using a SumIf function, I am able to sum all charges based upon the code but I've been starting fresh each month and deleting the previous month data. I'd like to just keep entering the charges into my spreadsheet and roll up the charges for each given month. Can I add another condition to my sumif function that will sum for a given date period (Jan 1 - Jan 31, Feb 1 - Feb 28) based upon that particular code?

Thanks.

In place of the SUMIF(), you'll need to construct a SUMPRODUCT() to replace it.

Code:

---

    A                  B                  C        D        E        G          H
1  Date        Description        Amount        Code                Start        1/1/2010
2  1/15/2010        Groceries        $50.00        GROC                End        1/31/2010
3  1/21/2010        Gas                $22.50        GAS       
4  1/25/2010        Groceries        $50.00        GROC                GROC        $100.00
5  1/30/2010        Gas                $22.50        GAS                GAS        $45.00
6  2/13/2010        Groceries        $50.00        GROC                               
7  2/13/2010        Gas                $22.50        GAS

---

The formula for H4 would be:
=SUMPRODUCT(($D$2:$D$100=G4) * ($A$2:$A$100>=$H$1) * ($A$2:$A$100<=$H$2) * $C$2:$C$100)

... copy that formula downward, it will adjust itself for each category.

Quote:

---

Originally Posted by JBeaucaire

In place of the SUMIF(), you'll need to construct a SUMPRODUCT() to replace it.

Code:

---

    A                  B                  C        D        E        G          H
1  Date        Description        Amount        Code                Start        1/1/2010
2  1/15/2010        Groceries        $50.00        GROC                End        1/31/2010
3  1/21/2010        Gas                $22.50        GAS       
4  1/25/2010        Groceries        $50.00        GROC                GROC        $100.00
5  1/30/2010        Gas                $22.50        GAS                GAS        $45.00
6  2/13/2010        Groceries        $50.00        GROC                               
7  2/13/2010        Gas                $22.50        GAS

---

The formula for H4 would be:
=SUMPRODUCT(($D$2:$D$100=G4) * ($A$2:$A$100>=$H$1) * ($A$2:$A$100<=$H$2) * $C$2:$C$100)

...copy that formula downward, it will adjust itself for each category.

---

Thanks, JB... it worked great!