1. ## Formula Help

Greetings,

I won do counting for two items, hope and real using mutiple criteria. The hope is a straight one(countifs), the real one i need help to introduce aditional criteria which is do counting when secondary exist in both real & hope. Appreciate any suggestion.

YEAR GENERAL SPECIFIC BRANCH SECONDARY STAGE GENERAL SPECIFIC BRANCH STAGE 2016
2016 FLY HI BUSH BBBC-485 HOPE FLY HI BUSH HOPE 4
2016 FLY HI BUSH BBBC-940 REAL REAL 4
2016 FLY HI BUSH BBBC-941 REAL
2016 FLY HI BUSH DRUM-600 REAL
2016 FLY HI BUSH DRUM-601 REAL
2016 FLY HI BUSH DRUM-602 HOPE
2016 FLY HI BUSH DRUM-602 REAL
2016 FLY HI BUSH DRUM-603 HOPE
2016 FLY HI BUSH DRUM-603 REAL
2016 FLY HI BUSH DRUM-604 HOPE
2016 FLY LOW BUSH DRUM-604 REAL
2016 FLY LOW BUSH DRUM-605 REAL
2016 FLY LOW BUSH RANK-818 REAL
2016 FLY LOW BUSH COOF-1000 REAL
2017 FLY HI CHRL COOF-231 HOPE
2017 FLY HI CHRL COOF-231 REAL
2017 FLY HI CHRL COOF-238 REAL
2017 FLY HI CHRL COOF-412 HOPE
2016 CALM HI BUSH COOF-412 REAL
2016 CALM HI BUSH COOF-441 REAL
2016 CALM HI BUSH COOF-442 REAL
2016 CALM HI BUSH COOF-443 REAL
2016 CALM HI BUSH COOF-460 REAL

2. I'm not sure of the format because the forum doesn't retain the formatting. Can you post your example as a spreadsheet?

cheers, Paul

3. Hi dubdub

If you are using Excel2007 or later, you can use the Countifs formula for multiple conditions.
See attached workbook for examples using your data.

zeddy

4. DubDub,

If I read your requirements correctly this formula should do the trick if placed in K2:
=COUNTIFS(\$A\$2:\$A\$24,K\$1,\$B\$2:\$B\$24,\$G2,\$C\$2:\$C\$24 ,\$H2,\$D\$2:\$D\$24,\$I2,\$F\$2:\$F\$24,\$J2)
dubdbucountifs.JPG
You'll note that I've added a 2017 column and changed one of your values (in yellow) to show that the formula is dragable right for additional years. I also added an additional criteria row to show it can be dragged down for additional criteria. However, if you want less or more criteria items you'll have to adjust the formula.

I would also suggest you create Dynamic Range names for Year, General, Specific, Branch, Secondary, and Stage. Then you won't have to worry about adding rows of data.

Formula w/Dynamic Range Names: =COUNTIFS(Year,K\$1,General,\$G2,Specific,\$H2,Branch ,\$I2,Stage,\$J2)

Here's a copy of my test worksheet w/Dynamic Range Names defined. dubdubhopereal.xlsx

Now, can you explain your second criteria a little more what do you want if a Secondary is present? Or can you figure it out from the above?

HTH

5. Thank you RetiredGeek for your reply, i have included definition and example of "REAL" count that I need in the attachment.

6. hi Paul, and sorry for my late reply. I have included an attachment.

7. Hi Zeddy, and sorry for my late reply. The " REAL" counts is not what i am after, i have included more explanation in my reply to RetiredGeek, i hope it is more clear.

8. If we can assume that a Secondary will only ever appear once for each of HOPE and REAL, you can use:
=COUNT(1/(FREQUENCY(IF((Year=K\$1)*(General=\$G2)*(Specific=\$ H2)*(Branch=\$I2),MATCH(Secondary,Secondary,0)),MAT CH(Secondary,Secondary,0))=2))
array entered with Ctrl+Shift+Enter

9. ## The Following User Says Thank You to rory For This Useful Post:

RetiredGeek (2014-12-22)

10. Hi dubdub

If, as Rory says, we can assume that a Secondary will only ever appear once for each of HOPE and REAL, then another way would be to use a 'helper' column to check for duplicates and then use SUMIFS.
see attached file
Rory's formula will work of course (although I would have used row 3 rather than row 2 in the formula) as shown in attached file

zeddy

11. Hi dubdub

..I should say, my method requires the data to be sorted, but Rory's doesn't.
So you should use Rory's method.
He has a brain the size of a planet.

zeddy

12. I just have a big head (literally and metaphorically) and people infer strange things...

13. Rory,

14. Thanks, RG.

I think your hat needs some tinsel for this time of year though.

15. Great thanks Rory, unfortunately, the appearance is more than once in each and asymmetric; are you indicating formula will be hard to develop under such situation.

16. Can you post a more representative sample of your data then? It should still be perfectly possible, just a little more complex.

Page 1 of 2 12 Last

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•