Array

[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

[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

"tghcogo" <[email protected]> wrote in
message news:[email protected]...
>
> 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
>

[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 changes.


Does that help?

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

[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