Combining COUNTIF and multiply

[Ian Plunkett]

I have a parts list for various products on Excel 2000. Sheet 1 is my data
base. Sheet 2 is my Quantities schedule.

The following picks out the number of occurencies of a particular value (an
item code) on a row from a sheet called "500-7,5":
=COUNTIF(('500-7,5'!$C$13:$C$154),A5)
This formula counts the number of time the item type is used; however there
is also a quantity involved.

In other words "Find all rows containing a particular value (item code) and
sum the value (quantity) in those rows.

Am I using the correct approach? Can anybody help me?

Ian Plunkett
Iptron Technology cc

[David McRitchie]

Hi Ian,
try SUMIF Worksheet Function

HTH,
David McRitchie, Microsoft MVP - Excel (site changed 2000-04-15)
My Excel Macros: http://www.geocities.com/davemcritchie/excel/excel.htm

Ian Plunkett <ipt...@pixie.co.za> wrote in message
news:OFLrbe7...@cppssbbsa02.microsoft.com...

[Ian Plunkett]

Tuesday 7:35 am Johannnesburg

Thank you David McRitchie

Your solution using SUMIF works fine. My working formula is:
=SUMIF('500-7,5'!C$13:C$155,A2,'500-7,5'!M$13:M$155)
where
'500-7,5" is the spreadsheet containing the parts list of a production
schedule.
'C$13:C$155' is the item code on that list in column 'C'.

'A2' is the item code on the summary spreadsheet.

'500-7,5'!M$13:M$155 is the quantities data on the production schedule in
column 'M'

If this is any help to someone else then good luck. There are so many
powerful and versatile function operators in Excel that it's hard to know
what their intended purpose is. Even with the User's Guide and the
elaborate help screens, understanding the commands can never be taken for
granted. A pointer from someone with experience is always welcome. Thanks
again to David McRitchie.

Ian Plunkett
IptronTechnology cc
Johannesburg, South Africa