CODEWITHSUNDEEP
excelworksheet-function
bartek
bartek

Reputation: 41

How to calculate sum of cells from chosen column which meets chosen conditions

I have an Excel table which contains 10 columns with object parameter values, every column has a label in the first row with a parameter name, and in the 11th column I have indicated the class to which the object belongs. I am trying to make a formula which can calculate the sum of parameter values for an object given a parameter name and class name. I need to chose a parameter name in a list field and class name in a list field and get the sum.

Upvotes: 1

Views: 3052

Answers (2)

Lance Roberts
Lance Roberts

Reputation: 22842

Create the two ListBoxes and then create a Linked Cell for each of them, the following formula uses N1 for the parameter Linked Cell, and O1 for the class Linked Cell.

=SUMIF(K:K, O1, INDIRECT(ADDRESS(1, MATCH(N1, A1:J1, 0))))

Upvotes: 1

MikeD
MikeD

Reputation: 8941

I am not 100% clear on the question, so I asume the following

  • a table with 11 column headings A1..K1
  • column headers A1..J1 are parameter names ("P1".."P10")
  • column header K1 is "Class Name"
  • multiple rows containing numeric entries in columns A..J and text entries in column K

The easiest would be to place an Autofilter on the header row and create a SUBTOTAL(109, ...) for sum or SUBTOTAL(102, ...) for count below the list for each parameter. Then filter the class and look up the total sum/count for each parameter.

An alternative would be to create a pivot table with P1 .. P10 in the data area (count or sum) and class as a page field .... then again you may select a distinct class and parameter to narrate the results

Finally, you can use the DSUM(), DCOUNT() or DCOUNTA() functions with

  • your table range (including headers!) as the database range
  • a field N2 with a list validation based on A1..J1 as field parameter (this is to select your parameter)
  • a range M1..M2 as the criteria parameter
    • M1 containing string "Class Name" (same as K1)
    • M2 containing the class name you want to look up - if you want a drop down list for this as well, you must have all class names in a seperate range, you cannot use column K for this as class names will not be unique there, but you can use the same validation for column K and the criteria

Hope that helps - good luck MikeD

Upvotes: 0

Related Questions