sumproduct help.

[angel]

My spreadsheet is

Australia ABC Company 200
XYZ Company 100
XXX Company 300
Australia Total 600

UK AAA Company 200
BBB Company 100
CCC Company 300
UK Total 600

Total: XXX

Now, I would let to set a formula for "TOTAL" in which, if column A cells
contains "total" (eg. Australia total, UK total), it will sum the respective
data in column C.

=sumproduct ((A1:A100="......." * (C1:C100))
i think the formula should be something like that.. but i dont know what to
put for "......"

thanks in advance.

 

[Max]

One way:
=SUMPRODUCT((ISNUMBER(SEARCH("Total",A2:A100)))*C2:C100)
--
Max
Singapore
http://savefile.com/projects/236895
Downloads:16,300 Files:356 Subscribers:53
xdemechanik

 

[Pete_UK]

Try this:

=SUMPRODUCT((ISNUMBER(SEARCH("total",A1:A100)))*(C1:C100))

Hope this helps.

Pete

 

[Bob Phillips]

Why not Data>SubTotals?