?
Solved

Count If function altered

Posted on 2014-04-23
4
Medium Priority
?
202 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
3 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 72

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

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

Do you use a spreadsheet like Microsoft's Excel?  Have you ever wanted to link out to a non excel file on your computer or network drive?  This is the way I found to do it!
If you need to forecast numbers -- typically for finance -- the Windows and Mac versions of Excel 2016 have a basket of tools to get the job done.
This Micro Tutorial will demonstrate in Microsoft Excel how to add style and sexy appeal to horizontal bar charts.
This Micro Tutorial demonstrates in Microsoft Excel how to consolidate your marketing data by creating an interactive charts using form controls. This creates cool drop-downs for viewers of your chart to choose from.

578 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