Help With My Checkbook

[Justinian]

I want to write a formula so that all expenses for January get summed in a cell below my checkbook labeled "January." I need a formula that sees if the word January is entered into a cell and if January is entered, no matter in how many cells, the formula adds those expenses into my total cell below which is also named January. The same applied for February and all months of the year. How do I do this?

 

[erik.van.geit]

You do not need any tool. Just copy from your worksheet and paste within a reply.
If you like you can try Table-It: on this forum you need the "CODE" system (you'll see if you want to use it)

 

[jamcall]

Cell A22
Running total

What formula do you have in A22?

 

[Justinian]

Ok, here it goes:

Vendor	Invoice Number	Description	Amount	Month
			$ 525.00	January
			$ 525.00	January
			$ 525.00	February
			$ 525.00	February
			$ 525.00	February
Jan	Feb	Mar	Apr	May	Jun	Jul	Aug	Sep	Oct	Nov	Dec

<tbody>
</tbody><colgroup><col span="12"></colgroup>

 

[erik.van.geit]

Under Amount you have "$ 525.00" which is considered as a string. Adding up strings will give you zero.
You must enter "525" and let the format add the "$" and the ".00".
Also you will make the formula easier when you use "Jan" or "January" and not a mix. Alternatively you could use numbers and format differently for the month names.

PS: avoid merging cells

 

[Charlie45]

In your example:

In A14 to get Junuary totals:
=SUMPRODUCT(--($K$1:$L$12="January"),$I$1:$J$12)

In B14 to get February totals:
=SUMPRODUCT(--($K$1:$L$12="February"),$I$1:$J$12)

 

[Justinian]

Under Amount you have "$ 525.00" which is considered as a string. Adding up strings will give you zero.
You must enter "525" and let the format add the "$" and the ".00".
Also you will make the formula easier when you use "Jan" or "January" and not a mix. Alternatively you could use numbers and format differently for the month names.

PS: avoid merging cells

I am confused. Adding January's numbers, two occurrences of $525, will equal $1,050. That is the number that should be input into the running total at the bottom. Same for February.

 

[Justinian]

In your example:

In A14 to get Junuary totals:
=SUMPRODUCT(--($K$1:$L$12="January"),$I$1:$J$12)

In B14 to get February totals:
=SUMPRODUCT(--($K$1:$L$12="February"),$I$1:$J$12)

Yes, this worked perfectly! Thank you!

 

[Charlie45]

Thanks for the feedback! glad I could help!

 

[erik.van.geit]

I am confused. Adding January's numbers, two occurrences of $525, will equal $1,050. That is the number that should be input into the running total at the bottom. Same for February.

I was talking about the real contents of your cells as displayed in your example. It's a question of format: do you understand the difference between the number itself and the way it is displayed (formatted)?

Yes that would indeed equal 1050 of course which you can achieve with sumproduct as I told you from start or with SUM.IF which is the "native" function to do so (also within my 1ste reply)
If it worked for sumproduct it must work for SUM.IF.
PROOF:

   A      B    C              D   E           F   G   H   I      J   K     L   
 1 Vendor      Invoice Number     Description             Amount     Month     
 2                                                        525        Jan       
 3                                                        525        Jan       
 4                                                        525        Feb       
 5                                                        525        Feb       
 6                                                        525        Feb       
 7                                                                             
 8                                                                             
 9                                                                             
10                                                                             
11                                                                             
12                                                                             
13 Jan    Feb  Mar            Apr May         Jun Jul Aug Sep    Oct Nov   Dec 
14 1050   1575 0              0   0           0   0   0   0      0   0     0   
checkbook
[Table-It] version 09 by Erik Van Geit

RANGE   FORMULA (1st cell)
A14:M14 =SUMIF($K$2:$L$12,A13,$I$2:$J$12)
[Table-It] version 09 by Erik Van Geit

 

[Justinian]

FYI, I did enter only 525 and allow the formatting to add the "$" and the ".00." I am not sure why you would assume it was a formatting issue when you did not know what kind of formatting my cells had.