CODEWITHSUNDEEP
excelexcel-formula
Martin Janáč
Martin Janáč

Reputation: 45

Count only one value for one ID

I'm having two columns: first contains values (in this case hours) and the second contains IDs. I need to sum just one value for one ID. I'm not sure if it is clear, but "solution" of this example should be 19,5

Unfortunately, this table is not prepared in some nice design, so I can't even use pivot table.

Any ideas? Thanks a lot.

screenshot here

Upvotes: 0

Views: 93

Answers (2)

Michael Gilchrist
Michael Gilchrist

Reputation: 1

A solution that may work would be to use an array formula:

{=SUM((A1:A5)/COUNTIF(B1:B6,B1:B6))}

This will divide every entry in column A by the count of the number of IDs before summing.

6,5    5    = 3,25
6,5    5    = 3,25
6,5    4    = 3,25
6,5    4    = 3,25
6,5    1    = 6,5
             ------
              19,5

In order to enter the formula as an array, do not type the {'s. Instead, use CTRL + SHIFT + ENTER instead of just ENTER when complete.

Upvotes: 0

Forward Ed
Forward Ed

Reputation: 9874

The solution I have uses a helper column. It assumes your values are column A and your IDs are column B.

In the column adjacent to IDs use the following formula:

=COUNTIF($B$2:B2,B2)

Then in the cell where you want your answer use the following SUMIF formula

=SUMIF(C2:C6,1,A2:A6)

Upvotes: 2

Related Questions