Formula assistance column match sum

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
 
T

T. Valko

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))
 
S

Shane Devenshire

Hi,

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

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

Shane Devenshire

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.
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top