Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
Solved

# Count If function altered

Posted on 2014-04-23
Medium Priority
198 Views
Dear Experts:

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.

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
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

LVL 11

Accepted Solution

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

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

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

Question has a verified solution.

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

When you see single cell contains number and text, and you have to get any date out of it seems like cracking our heads.
This article describes a serious pitfall that can happen when deleting shapes using VBA.
The viewer will learn how to use a discrete random variable to simulate the return on an investment over a period of years, create a Monte Carlo simulation using the discrete random variable, and create a graph to represent the possible returns over…
Graphs within dashboards are meant to be dynamic, representing data from a period of time that will change each time the dashboard is updated with new data. Rather than update each graph to point to a different set within a static set of data, t…
###### Suggested Courses
Course of the Month7 days, 4 hours left to enroll