Criteria Based Counting

[Dominiks]

Hi All,

I am attempting to count the total number of times certain criteria appear over multiple columns in a large data set.

Column A - Criteria Range
Column B - 1, 2, 3 or blank
Column C - 1, 2, 3 or blank
Column D - Numeric Value +ve/-ve or blank

Condition;

Criteria Range matches criteria & value of C<=B and D >30

Formula;

COUNTIFS('SheetRef'!$A:$A,$CriteriaCell,'SheetRef'!$C:$C,"<="'SheetRef'!$B:$B,'SheetRef'!$A:$A,$CriteriaCell,'SheetRef'!$D:$D,">30")

I have successfully used a similar formula when the criteria is a specific value, but I cant get it to work when the criteria is another corresponding value in another column. Any help on this would be much appreciated.

[davesexcel]

Attaching a sample workbook enables others to work on your problem:

To attach a sample workbook. Make sure there is just enough data to make it clear what is needed. Include BEFORE/AFTER sheets if needed to show the process you're trying to complete or automate. Remember to desensitize the data.

Click on GO ADVANCED and click "manage attachments" to open the upload window.


To add a file to a post

[Phuocam]

Try this ...

SUMPRODUCT(('SheetRef'!$A1:$A1000=$CriteriaCell)*('SheetRef'!$C1:$C1000<='SheetRef'!$B1:$B1000)*('SheetRef'!$D:$D>30)

[Dominiks]

Many thanks Phuocam I managed to get this working!

I had to add in an additional condition to not count blanks as the formula would include them if the referenced cells had formulas in them. Cant work out why it does that but when I tried it in a simplified sheet without formula in them it worked so can only think that is what caused it. As soon as that was added in it works a treat and I greatly appreciate the help.

Kind Regards

Dominik

[daddylonglegs]

Cant work out why it does that ......

Excel considers any text value to be greater than any number.....and a “formula blank” returned by a formula is also a text value