Count Criteria Based on Another Criteria

[ilcaa]

i have 2 columns. 1 with categories, another with values
Categories, Values
cat1, 10
cat1, 2
cat2, 3
cat2, 5
cat2,10
cat3, 6
etc

i would like to sum the value based on a specific category, so the formula would return 12 for cat1, 18 for cat2, 6 for cat3

 

[mrmmickle1]

Have you looked at the SUMIF function?

https://support.office.com/en-sg/article/SUMIF-function-169b8c99-c05c-4483-a712-1697a653039b

 

[Amberv83]

Have you tried a pivot table?

 

[xfuxp100]

Hi,

I would use the formula SUMIFS. It allows you to sum values based on 1 and more conditions.

Lets say I have category in column A, and values in column B
=SUMIFS(B2:B7;A2:A7;"cat1") <--- instead of "cat1" you can use reference to a cell with the same name

'=SUMIFS(values;category;category you want to be summed up)
it sums up all values that are in the category column and are defined as "cat1".

In case you would use older version 2003, then you should use SUMPRODUCT.
Pavla

 

[ilcaa]

i made a mistake in the above, the sum of the values is easy because SUMIF allows SUMIF( range, criteria, [sum range]) i want to apply the same process to a Count if, so intinitial range, criteria of that initial range, then count values of another range

=countif(categories, "cat1", values)