Summing cells when adjacent cell contains certain values

tamure

New Member
Joined
Oct 4, 2006
Messages
2
Greetings all,

I am an excel newbie, so please bear with me. I'm using Excel 2003 on Windows 2000 and Windows XP.

I'm creating a spreadsheet to help track personal spending, budget, etc. I want to create a formula that will calculate the net amount of credit card transactions. The spreadsheet contains a "register" where you enter the transactions, including date, description, and amount. There is also a pulldown menu to choose what type of transaction it is. This menu includes the names of the user's credit cards which are entered by the user on a separate sheet, plus "Savings" and "Flex" which is a slush fund. It looks like this:

excel.jpg


Notice the $0.00 in "Net personal credit transactions." What I want to do is sum only those amounts where the column immediately to the left of it contains the name of one of the credit cards. In other words, don't sum it if the column to the left contains 0, Flex, or Savings. All other possible values will be credit cards.

The current formula is: {=SUM(IF(OR(I10:I29="Flex",I10:I29="Savings",I10:I29=0),0,J10:J29))}

So, my (apparently mistaken) understanding is that the OR function will return TRUE if any of those conditions are met, i.e., that the I column value is 0, Flex, or Savings. That should kick the TRUE out to the IF function, which ought to return 0. Otherwise, it will return the value in the J column. Whether IF kicks out 0 or the J column value, these should be summed by SUM. The result should be only the values that correspond to a credit card transaction. Shouldn't it?

Anyway, it appears that if it ever encounters a I column cell that contains anything except a credit card name, the result is 0. If all the I column cells contain a credit card name, the calculation will work. I don't want to have to redo the budget sheet just to make this work. It seems like it ought to work as it is, but I must be missing something.

Perhaps my trouble is that I misunderstand how arrays work. My understanding is that:

(1) Take I4, see if any of the conditions are true, return either 0 or J4, and SUM stores that (0 or J4) in some variable
(2) Go to I5, see if any of the conditions are true, return either 0 or J5, and SUM adds that (0 or J5) to whatever is stored in it's variable
(3) Wash, rinse, repeat.

Or, is it starting at I4, and check all cells to I25, and if any of those values is not a credit card, returns 0, then starts at I5, and checks to I25, etc., so that SUM gets a whole bunch of 0's?

If in fact it is the latter, is there anyway to make it do the former? I'd prefer not to use macros if possible. Sorry for the long post, I hope this is clear and adequately explains the difficulty I'm having.
 

Excel Facts

Copy a format multiple times
Select a formatted range. Double-click the Format Painter (left side of Home tab). You can paste formatting multiple times. Esc to stop

hatman

Well-known Member
Joined
Apr 8, 2005
Messages
2,664
Welcome to the board!

Starting from your formula
Code:
{=SUM(IF(OR(I10:I29="Flex",I10:I29="Savings",I10:I29=0),0,J10:J29))}
you may want to know that for some reason And() and Or() simply do not work in array functions. You probably have a slew of options, but I can only think of 2, off the top of my head:

Code:
{=SUM(IF(I10:I29="Flex",J10:J29,if(I10:I29="Savings",J10:J29,if(I10:I29=0,J10:J29,0),0),0))}

or

Code:
=sum(sumproduct(--(I10:I29="Flex"),J10:J29),sumproduct(--(I10:I29="Savings"),J10:J29),sumproduct(--(I10:I29=0),J10:J29))
 

Brian from Maui

MrExcel MVP
Joined
Feb 16, 2002
Messages
8,458
Little confused on you criteiras but,

=SUMPRODUCT(--(ISNUMBER(MATCH(I10:I29,{"Flex","Saving",0},0))),J10:J29)

Since you're on 2003, consider the List feature in 2003 and use a Pivot Table.
 

tamure

New Member
Joined
Oct 4, 2006
Messages
2
you may want to know that for some reason And() and Or() simply do not work in array functions.

Well, that explains it! Thank you for verifying my sanity. Now off to read up on these other ways of skinning the cat.

Thank you hatman and Brian, I will give those suggestions a try. :biggrin:
 

Forum statistics

Threads
1,141,628
Messages
5,707,503
Members
421,511
Latest member
mgroah1

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top