COUNT IF TEXT- Change from Non-LARC to LARC Methods

[Marvin85]

Dear friends,

I have a spreadsheet that contains two columns: column A represents the patient contraceptive method when they arrive to the clinic and column B, the patient contraceptive methods when leaving the clinic. There are 8 contraceptive types:

IUD, implant (LARC)
Depo-Provera, Pill, patch, ring, none, other (Non-LARC)

My goal is to count the instances (frequency and %) where the patient went from Non-LARC to a LARC contraceptive method (C17, D17). for example: from ring to IUD. We should not count instances where it was LARC to LARC. for example. from IUD to implant. My initial strategy was to add a column first to count each instances of change (column C). and then count that. But if there is way to count this directly, it would be great. Either way would work

Please see the attached document. I really would appreciate any help on this.

BEst,
Marvin

[berlan]

Hi,

given the structure of the sheet, with 1's and 0's, you could try:

C17: =COUNTIF(C2:C12,1)
D17: =AVERAGE(C2:C12)

Cheers,
berlan

[FlameRetired]

My initial strategy was to add a column first to count each instances of change (column C). and then count that. But if there is way to count this directly, it would be great.

Try this in C17

Please Login or Register  to view this content.

and this in D17

Please Login or Register  to view this content.

If your count in upload C3 is a typo these should work. (A3 is "Other" (non-Larc) and B3 is "IUD" (Larc)). Your count is 0. Shouldn't that be 1?

[Marvin85]

Hi BErlan,

Thanks for your help. But I also need the formula for column C. Can you help me with that?

[Marvin85]

Dave,

This is amazing. I didnt know Excel was that powerful. When I can learn about this command? Is there a source I can look at so I can understand what you did. I would like to nail this.

Thank you again!
Marvin

[FlameRetired]

Dave,

This is amazing. I didnt know Excel was that powerful. When I can learn about this command? Is there a source I can look at so I can understand what you did. I would like to nail this.

Thank you again!
Marvin

You're welcome. Thanks for the feedback.

I assume you speak of SUMPRODUCT. I share your amazement. In fact reports are that MS has been amazed at some of the creative things people are doing with this function ….. things well beyond what MS anticipated.

I started here:
http://www.xldynamic.com/source/xld.SUMPRODUCT.html

I followed that up with a little “tutorial” that applies that knowledge here:

http://exceluser.com/ideas/benford_xl12.htm

It’s a little SUMPRODUCT exercise used with something called Benford’s Law. It is applied to the stock market. It makes heavy use of the principles laid down in that first link. The principles are applicable in a wide variety of formulas.

Hope you have fun. I did.

Hope this helps.