Formula assistance column match sum

  • Thread starter Thread starter JB Akron
  • Start date Start date
J

JB Akron

Please take a look at this formula.

=IF(COUNTIF(F$2:F$9,F2)=COUNTIF(F$2:F2,F2),IF(COUNTIF(K$2:K$9,K2)=COUNTIF(K$2:K2,K2),SUMIF(F$2:F2,F2,K$2:K2,K2,L$2:L2),"")

There is an error?

I would like to get a sum- if column F and K are identical. The numbers to
be summed are located in Column L.

Example:

F K L sum
s100 species 1 2
s100 species 1 4 6
s101 species 2 1 1
s102 species 3 2
s102 species 3 5 7
....
.....
.....
s1000 species 1 4
s1000 species 1 5 9
 
This seems to work.

Entered in M2 and copied down as needed. Cell M1 *must* not contain a
number. Also assumes the data is sorted or grouped together by column F as
is shown in your sample data.

=IF(F2&K2=F3&K3,"",SUM(L$2:L2)-SUM(M$1:M1))
 
Hi,

If I consider your stated goal and not your formula or your example:

=SUMPRODUCT((F1:F6=K1:K6)*L1:L6)
 
Hi,

Some additional comments, if I take your data and not you stated objectives
then the formula could be

=IF(COUNTIF(F$2:F$9,F2)=COUNTIF(F$2:F2,F2),SUMIF(L$2:L2,L2,L$2:L2),"")

Note that with your sample data the column K has no effect on the results,
this could be just bad luck because you choose to show this particular data
or it could be that you really don't need to consider that column.
 
Back
Top