Solved

Count If function altered

Posted on 2014-04-23
4
189 Views
Last Modified: 2014-04-24
Dear Experts:

Could somebody please help me in automating a computation

The macro or formula is to search for a specific criterion (e.g. 'not functional') in all the cells of Column B. If found, the macro/formula has to sum up the values in Column A on the same row. The macro/formula will repeat this for all the criteria.

The listing of the  single criterions under the heading 'Overall Result' (in Column C and D) will be already there if the macro / formula is run / activated.

The data in Column A and B is not to be sorted, i.e. no sorting is allowed.

I have attached a sample file for your convenience.

Thank you very much in advance for your valuable help.

Regards, Andreas

COLUMN A and COLUMN B

Count      Criteria
4              not functional
3              severly damaged
2              not functional
5              minor damage
1              burnt
6              severly damaged
2              not functional
4              minor damage
2              burnt


COLUMN C AND COLUMN D      
      
Overall Result      
not functional                8
severely damaged          9
minor damage                9
burnt                              3

CountIf.xlsx
0
Comment
Question by:AndreasHermle
4 Comments
 
LVL 10

Accepted Solution

by:
JEaston earned 250 total points
ID: 40017553
If I understand you correctly, you should use the SumIf function.

Therefore in Cell D2 copy this in:   =SUMIF($B$2:$B$10,C2,$A$2:$A$10)

You can then copy this down for the other totals in Column D.
0
 
LVL 68

Assisted Solution

by:Qlemo
Qlemo earned 250 total points
ID: 40017562
Put the following formula into D2, and drag it down:
  =sumif(B:B, C2, A:A)
0
 

Author Comment

by:AndreasHermle
ID: 40019430
Dear both,

ups, of course ... I have used SumIF several times before but for some reason did not grasp that in this case it is the formula to use.

Great, thank you very much. I suggest splitting the points.

Again, thank you very much for your swift help.

Regards, Andreas
0

Featured Post

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

INDEX and MATCH can be used to great effect to replace HLOOKUP and VLOOKUP as it does not have the limitation of needing the data to be sorted so that the reference value is in the first column or row. It also has the ability to perform a bi-directi…
Drop Down List with Unique/Distinct Values (Part II - ComboBox or ListBox and Data Validation List Bonus!) David Miller (dlmille) Intro This article focuses on delivering unique, sorted lists to list objects (e.g., ComboBox, ListBox) and Dat…
The viewer will learn how to simulate a series of coin tosses with the rand() function and learn how to make these “tosses” depend on a predetermined probability. Flipping Coins in Excel: Enter =RAND() into cell A2: Recalculate the random variable…
This Micro Tutorial demonstrates how to create Excel charts: column, area, line, bar, and scatter charts. Formatting tips are provided as well.

911 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

20 Experts available now in Live!

Get 1:1 Help Now