Counting Multiple Entries...

I am adding an a new sheet to an existing Excel project. The original sheet is an attendence tracker using COUNTIF (referenced to absolute cells) to track Lates, Vacation days, Sick ect. On this sheet, on any given day a person will be only one of 4 things ranging from present to sick to late and so on.

The new sheet is to keep track of account errors. I copy & pasted the first sheet and changed the names of the cells from late and so on to the new catagories. The problem is, this time, in any given cell there may be mutiple entries (HL, SR ect.).

My question is how to seperate the text (HL:SR or HL,SR for example) and how do I count each entry individually?

I appreciate any help on this matter greatly!!

Joe

Originally Posted by joseph_b

I am adding an a new sheet to an existing Excel project. The original sheet is an attendence tracker using COUNTIF (referenced to absolute cells) to track Lates, Vacation days, Sick ect. On this sheet, on any given day a person will be only one of 4 things ranging from present to sick to late and so on.

The new sheet is to keep track of account errors. I copy & pasted the first sheet and changed the names of the cells from late and so on to the new catagories. The problem is, this time, in any given cell there may be mutiple entries (HL, SR ect.).

My question is how to seperate the text (HL:SR or HL,SR for example) and how do I count each entry individually?

I appreciate any help on this matter greatly!!

Joe

I have read this a few times and cannot determine if you want to count all items that 'contain' "HR" or if you want to count (the unlikely) how many times 'HR" appears within the item.

Assuming the former, then

=SUMPRODUCT(--(NOT(ISERROR(FIND("HR",A1:A6))))*(--(K1:K6=1)))

should go close.

hth
---

Thanks for the response Bryan.

I do in fact need to count the total amount of "HL"'s and "SR"'s. It wasn't a problem using COUNTIF when it was for attendance, because there was only one (or no) entry per cell for any given day. Now using this to track account errors, there may be multiple errors per cell for any given day, for each employee. I hope I am being clear, it's hard to explain without seeing the wooksheet.

I have attached the actual spreadsheet for you all to view.

Thanks,

Joe

Originally Posted by joseph_b

Thanks for the response Bryan.

I do in fact need to count the total amount of "HL"'s and "SR"'s. It wasn't a problem using COUNTIF when it was for attendance, because there was only one (or no) entry per cell for any given day. Now using this to track account errors, there may be multiple errors per cell for any given day, for each employee. I hope I am being clear, it's hard to explain without seeing the wooksheet.

I have attached the actual spreadsheet for you all to view.

Thanks,

Joe

Viewing the workbook provided little information as there does not appear to be any cell that contains more than a simple 'HL'

I see no purpose in worksheets other than 'Acct Errors' as these appear devoid of any useful information and contain no data.
On Acct Errors I see only one formula, at E33 testing two ranges, columns 'G' to 'CI' and then column 'K'

The column K test is, of course, always going to fail, and, as your request is not for a 'multiple' test, this can simply be omitted.

The column G to CI test is, correctly, returning 0 as you have but a single simple 'HL' in cell AB12

The "HL" in the formula can be replaced with D$3 - also, if you replace $D$4 in your existing counif formula with D$4 you can formula-fill sideways then bulk-fill downwards instead of (as they say) 're-inventing the wheel' for each column.

-------------------------
for example, in C4 put

=COUNTIF($G4:$CI4,C$3)

and formula-fill sideways to F4, then with those cells all highlighted, bulk-fill downwards.
It's much quicker to setup that way.

-------------------------

for the (possible) multiple entries per cell, the formula for C4 is:

=SUMPRODUCT(--(NOT(ISERROR(FIND(C$3,$G4:$CI4)))))

Fill that sideways, then bulk-fill down.

hth
---