Home |
Search |
Today's Posts |
#1
|
|||
|
|||
Array
I have a spreadsheet of various types of expenditure, for example: Date in column A Column B Visa chq cash DD Visa cash chq chq etc with the values in column C I am trying to use sumif to have subtotals of the different types of expenditure. I'm missing something obvious............. well it is Friday pm thanks -- tghcogo ------------------------------------------------------------------------ tghcogo's Profile: http://www.excelforum.com/member.php...o&userid=10494 View this thread: http://www.excelforum.com/showthread...hreadid=484260 |
#2
|
|||
|
|||
Array
=SUMPRODUCT(--(B2:B10="Visa"),C2:C10)
or =SUMIF(B2:B10,"Visa",C2:C10) assuming the amounts are in C will sum for Visa if you want to include dates from A you need sumproduct =SUMPRODUCT(--(A2:A10=DATE(2005,10,30)),--(B2:B10="Visa"),C2:C10) will sum column C when A is Oct 30 2005 and B is Visa Regards, Peo Sjoblom "tghcogo" wrote in message ... I have a spreadsheet of various types of expenditure, for example: Date in column A Column B Visa chq cash DD Visa cash chq chq etc with the values in column C I am trying to use sumif to have subtotals of the different types of expenditure. I'm missing something obvious............. well it is Friday pm thanks -- tghcogo ------------------------------------------------------------------------ tghcogo's Profile: http://www.excelforum.com/member.php...o&userid=10494 View this thread: http://www.excelforum.com/showthread...hreadid=484260 |
#3
|
|||
|
|||
Array
Two methods come to mind: Method 1 (Subtotals): •Make sure columns have Headings (Date, Payment Type, Amount, etc) •Sort your list by Payment Type •Select the whole list •DataSubtotals -At each change in: Payment Type -Use Function: SUM -Add Subtotal to : Amount •Click OK Method 2 (Pivot Table): •Make sure columns have Headings (Date, Payment Type, Amount, etc) •Select your list •DataPivot Table -Excel list -The range should already by selected, but adjust if necessary -Click the [layout] button -Drag the Payment Type box to ROW -Drag the Amount box to DATA -(If the new label says Count of Amount, double click it and change to SUM) -Click [OK] -Select either New Worksheet or Select a location in the current sheet for the Pivot Table -Click Finish •Now, as you add data to the list, you can adjust the Pivot Table source range and click [Finish] for the latest totals Note: Pivot Tables only update on demand, not each time the data changes. Does that help? ••••••••••••• Regards, Ron -- Ron Coderre ------------------------------------------------------------------------ Ron Coderre's Profile: http://www.excelforum.com/member.php...o&userid=21419 View this thread: http://www.excelforum.com/showthread...hreadid=484260 |
#4
|
|||
|
|||
Array
thanks Peo, =SUMIF(B2:B10,"Visa",C2:C10) worked fine, my formula was the same, but for some reason wouldn't work, untill I cleared all the cells and pasted yours in!!!! Ron, thanks for your advice, I didn't need to go that route in the end, but it looked an interesting way of getting the answer. Thanks to both of you -- tghcogo ------------------------------------------------------------------------ tghcogo's Profile: http://www.excelforum.com/member.php...o&userid=10494 View this thread: http://www.excelforum.com/showthread...hreadid=484260 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Pass an array to Rank | Excel Worksheet Functions | |||
Match / Vlookup within an Array formula | Excel Discussion (Misc queries) | |||
Where is the bug in my array? | Excel Discussion (Misc queries) | |||
Formula to list unique values | Excel Worksheet Functions | |||
VBA Import of text file & Array parsing of that data | Excel Discussion (Misc queries) |