Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

Count If function altered

Posted on 2014-04-23
4
Medium Priority
?
200 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 11

Accepted Solution

by:
John Easton earned 1000 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 71

Assisted Solution

by:Qlemo
Qlemo earned 1000 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

Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

Microsoft's Excel has many features that most people will never need nor take advantage of.  Conditional formatting is one feature that you may find a necessity once you start using it.
This article describes how to use a set of graphical playing cards to create a Draw Poker game in Excel or VB6.
This Micro Tutorial will demonstrate in Microsoft Excel how to add style and sexy appeal to horizontal bar charts.
Although Jacob Bernoulli (1654-1705) has been credited as the creator of "Binomial Distribution Table", Gottfried Leibniz (1646-1716) did his dissertation on the subject in 1666; Leibniz you may recall is the co-inventor of "Calculus" and beat Isaac…

886 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