Conditional Counting

1. Conditional Counting

Hi Folks,
I have a data set with 4 columns. The first three are informational. Column A has regions (3 different titles), B has Business (6 different titles) and C has Style (many different titles). Column B has the total # of orders. I would like to count the # of styles for each of the 6 businesses by region. So 1 equation would be Count the # of Styles if Column A = APAC and Column B = kids.

I'm not sure what I am doing wrong but I can't get the equation to work.

thanks

2. Re: Conditional Counting

Hi,
Rather than a formula, which is rather hard to comment about when we don't know what it is ( ) try a pivot table instead.

This will no doubt give you what you know you want along with other information that you don't yet know that you want, plus information that you don't want but that you will get anyway - shades of Rumsfeld creeping in here I fear

3. Re: Conditional Counting

Did you tried with COUNTIFS?

4. Re: Conditional Counting

Because of how the rest of the info is organized/works it would be much more helpful to just use a conditional countif statement. The statement I was going with was =countifs(A:A,"APAC",B:B,"Kids")

5. Re: Conditional Counting

Then a sample workbook will be useful.

6. Re: Conditional Counting

Attached is an example. I also would like to count repeats as one. (If a style appears more than once, it should only count as one).

7. Re: Conditional Counting

Hi,

Why did you reject the Pivot Table approach (post #2) out of hand?

Your data is arranged perfectly for a Pivot Table -see attached

8. Re: Conditional Counting

A pivot table doesn't work with the rest of the data in the workbook. This is only part of an even larger series of equations and a pivot table would be a problem.

9. Re: Conditional Counting

Your formula gives as result 49.

=countifs(A:A,"APAC",B:B,"Kids")

Which is the expected result and why?

10. Re: Conditional Counting

I'm sorry, you're right and I didn't make this clear before. I would like to count repeats as one. So the first two rows are a repeated style, and I would like to count them as 1, since this is a count of the # of styles in each business and region. I was thinking perhaps that same countifs statement - # of repeats somehow?

11. Re: Conditional Counting

In F2 and copy down. This will be a helper & hidden if you like column.

=COUNTIFS(\$A\$2:A2,A2,\$B\$2:B2,B2,\$D\$2:D2,D2)

Then use this one.

=COUNTIFS(A:A,"APAC",B:B,"Kids",F:F,1)

12. Re: Conditional Counting

Almost got it. However, some of the cells in columns Total 2012 and 2013 have a value of 0. If there is a 0, the style should not be counted for that year, however with the following equation =COUNTIFS(\$D\$2:D2,">0",\$C\$2:C2,C2), cells with a 0 sometimes return a 1 instead of a 0. Please see the attched example, row 18 and 20 specifically.

example.xlsx

There are currently 1 users browsing this thread. (0 members and 1 guests)