Solved

Count If function altered

Posted on 2014-04-23
4
197 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
4 Comments
 
LVL 11

Accepted Solution

by:
John Easton 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 70

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

On Demand Webinar: Networking for the Cloud Era

Did you know SD-WANs can improve network connectivity? Check out this webinar to learn how an SD-WAN simplified, one-click tool can help you migrate and manage data in the cloud.

Question has a verified solution.

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

Freeze panes is an option within all variants of Excel to enable parts of a sheet to remain stationary when the cursor is in another part of the sheet. This is a very useful feature which is overlooked or under used.
Some code to ensure data integrity when using macros within Excel. Also included code that helps secure your data within an Excel workbook.
The viewer will learn how to use the =DISCRINV command to create a discrete random variable, use this command to model a set of probabilities and outcomes in a Monte Carlo simulation, and learn how to find the standard deviation of a set of probabil…
This Micro Tutorial will demonstrate how to use longer labels with horizontal bar charts instead of the vertical column chart.

624 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