Need the excel formula for following function

Question

I need help to do the following function in a MS Excel sheet. The sheet example is as follows

       A              B         C          D              E 
1     TimeStamp       Name     Amount     UsedBy        Description
     -----------------------------------------------------------
2     Date1           Me1       200       He1,She1        desc1
3     Date2           Me1       100       Me1,He1         desc2
4     Date3          She1        50       He1,She1,Me1    desc3
5     Date4           He1        70       She1,He1        desc4
6     Date5          She1       200       She1,He1,Me1    desc5
7     Date6           Me1        22       He1             desc6

I want some function which can do the following sequence of job in a single customized MS-Excel formula

Sum the cells of column "Amount" where "UsedBy" column cells contain "He1" as a single entity. Lets say result is X
Sum of the cells of column "Amount" where "UsedBy" column cells contain two entities and "He1" must be one entity. After this sum devide it by 2. Lets say result is Y.
Sum of the cells of column "Amount" where "UsedBy" column cells contain three entities and "He1" must be one entity. After this sum devide it by 3. Lets say result is Z
Total the result in steps 1,2 and 3. That means the sum of X+Y+Z

Please let me know if I am not clear in my question....

I don't know Excel, but I would suggest you approach this from the aspect of summing "Amount divided by 'number of commas in UsedBy plus 1" for those UsedBy rows containing He1. — Will A
– Will A, Commented Jul 11, 2010 at 17:49

buckbova · Accepted Answer · 2010-07-11 17:50:03Z

4

Try the SUMIF function.

answered Jul 11, 2010 at 17:50

buckbova

1,2336 silver badges11 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Mau · Accepted Answer · 2010-07-11 17:55:45Z

1

Build some intermediate results like the number of values in UsedBy, or whether UsedBy contains He1 in separate columns, then use SUMIF().

answered Jul 11, 2010 at 17:55

Mau

14.5k2 gold badges33 silver badges53 bronze badges

Comments

Dean MacGregor · Accepted Answer · 2015-06-06 19:09:59Z

You can't do this in a single formula unless you write it yourself in VBA. Since you haven't tagged the question as VBA I'll assume you'd rather use helper columns.

You'll need 3 helper columns, 1 for each of your criteria.

For your first let's say you put it in column F

=if(and(isnumber(search("He1",D2)),len(d2)=len(substitute(d2,",",""))),1,0)

What this does is ensures that D2 contains 'He1' and makes sure there are no commas.

For your second put it in column G

=if(and(isnumber(search("He1",D2)),len(d2)-1=len(substitute(d2,",",""))),1,0)

What this does is ensures that D2 contains 'He1' and makes sure there is 1 comma.

For your third put it in column H

=if(and(isnumber(search("He1",D2)),len(d2)-2=len(substitute(d2,",",""))),1,0)

What this does is ensures that D2 contains 'He1' and makes sure there are 2 commas.

Once you have your helper criteria columns you can now do a sumif for each critera.

For X you'll do =sumif(f2:f7,1,c2:c7)

For Y you'll do =sumif(g2:g7,1,c2:c7)/2

For Z you'll do =sumif(h2:h7,1,c2:c7)/3

Collectives™ on Stack Overflow

Need the excel formula for following function

3 Answers 3

Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related