Solved

Count If function altered

Posted on 2014-04-23
4
194 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 69

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

Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

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

Suggested Solutions

Title # Comments Views Activity
Convert SuperCalc files to Excel 9 44
VBA modules import 4 56
MS Excel Multi Sheet Formula 13 31
Excel 2010 Pivot Table Calculated Fields 2 10
Approximate matching with VLOOKUP and MATCH seems to me to be a greatly under-used technique, and one which is vital for getting good performance out of large lookups. Until recently I would always have advised using an exact match for simplicity an…
This article will guide you to convert a grid from a picture into Excel format using Microsoft OneNote and no other 3rd party application.
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…
Finds all prime numbers in a range requested and places them in a public primes() array. I've demostrated a template size of 30 (2 * 3 * 5) but larger templates can be built such 210  (2 * 3 * 5 * 7) or 2310  (2 * 3 * 5 * 7 * 11). The larger templa…

839 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