Array

T

tghcogo

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
 
P

Peo Sjoblom

=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
 
R

Ron Coderre

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
•Data>Subtotals
-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
•Data>Pivot 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
Click to expand...
changes.


Does that help?

•••••••••••••
Regards,
Ron
 
T

tghcogo

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
 
Top