Take the 2-minute tour ×
Super User is a question and answer site for computer enthusiasts and power users. It's 100% free, no registration required.
=SUMPRODUCT((F16:F274=("A"))  *(F17:F275<>("A")))
+SUMPRODUCT((F16:F274=("AH")) *(F17:F275<>("AH")))
+SUMPRODUCT((F16:F274=("AU")) *(F17:F275<>("AU")))
+SUMPRODUCT((F16:F274=("AHU"))*(F17:F275<>("AHU")))

I am using the above formula to add the number of occasions sickness occurs using the following as a key. It works fine until you get say an A and a AH in the same sickness period.

Instead of reporting just one occasion off it reports two. Is there a way I can separate this?

Absence A
Absence 1/2 AH
Absence Unpaid  AU
Absence 1/2 Unpaid  AHU
share|improve this question
    
Please give a better explanation of what your data mean, what you are trying to accomplish, and what result you want to get. –  Scott Sep 18 '12 at 17:31
    
Hi Scott I am trying to calculate the Brandford Factor for HR and want the formula to return the individual number of grouped period of absence regardless of which denominations of the folowing the period contains Absence A Absence 1/2 AH Absence Unpaid AU Absence 1/2 Unpaid AHU currently I can only get it to work if each period only contains on type of absence. I trust this is clearer –  user158056 Sep 19 '12 at 14:06
    
@user158056: In the future, if you clarify a question, consider doing it by editing the question – see the How do I ask questions here? section of the FAQ. –  Scott Sep 19 '12 at 21:29

1 Answer 1

I'm still not 100% sure that I understand the question, but if none of your non-absence codes begin with "A", then

=COUNTIFS(F16:F274, "=A*", F17:F275, "<>A*")

might be what you want.

share|improve this answer
    
Isn't this formula (almost) always going to give the answer 1? –  pnuts Sep 20 '12 at 0:59
    
@pnuts: That seems to be what user158056 wants. From the question: “I am using the above formula … It works fine until you get, say, an ‘A’ and an ‘AH’ in the same sickness period. (\n) Instead of reporting just one occasion off it reports two.” And from the comment: “I … want the formula to return the individual number of grouped periods of absence regardless of which denominations … the period contains”. –  Scott Sep 20 '12 at 16:13
    
I suspect I have made a different guess about the OP's data format from you. But OP says formula in question works in general and as it seems likely to provide answers greater than 1 an alternative that is (virtually) only capable of 1 does not look promising. –  pnuts Sep 20 '12 at 17:06
    
@pnuts: OK, this time I misunderstood your comment. No, my formula is not (almost) always going to give the answer 1. For example, if F16:F20 contain { ‘A’, ‘P’, ‘A’, ‘P’, ‘A’ }, my formula evaluates to 3. –  Scott Sep 20 '12 at 19:44
1  
@user158056: By the way, when you answer somebody’s question (from a comment) in a new comment, it’s conventional to mention that person’s name, preceded by “@”, as in “@Scott, @pnuts”. That way we get notified. See the Replying in comments paragraphs of the Comment formatting section of the Markdown Editing Help page. –  Scott Sep 24 '12 at 17:52

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.