Looking how to Count the number of times "Oct" is used in a column

[foxy_420]

Hello,

My document contains various lab test values (A1C values), and I want to be able to count the number of times that the value is >= 7 for each giving month.

To try and make this more clear, I want to count how many times A1C value => 7 in "Oct".

I currently have two columns, one with A1C values(B column) and the other one with dates (C column). The format of the dates is "Oct-2012". The field is in fact a date and NOT a text field.

Can anyone help me count how many times A1C value => 7 in "Oct" ?



This is what I've tried to no avail:
=COUNTIFS(B:B, ">=7",C:C,"*Oct*")
(I have successfully counted the number of times A1c >=7, but cant add the "oct" part in successfully).

[Norie]

lucy

Are the values in column C actual date values or are they text like Oct?

If they are dates try this.

=SUMPRODUCT(--(B:B>=7), --(Month(C:C) = 10))

[foxy_420]

Thanks for the reply,

I did try what you said but it keeps on giving me the
#VALUE" error.

The values in Columns C are in fact a date. The appear in the format "Oct-2012", but when I click on a date cell they are really in the format "01/10/2012"

[Norie]

What version of Excel are you using?

Can you attach a sample workbook?

[foxy_420]

I am using version 2007.

Is there a simple way to attach a workbook?
I can attach a picture but that would probably be more problematic.

Diabetes pic.jpg

here is the picture, I don't believe I have privileges to upload my excel file.

[FDibbins]

To Attach a File:

1. Click on Go Advanced
2. In the frame Attach Files you will see the button Manage Attachments
3. Click the button.
4. A new window will open titled Manage Attachments - Excel Forum.
5. Click the Browse... button to locate your file for uploading.
6. This will open a new window File Upload.
7. Once you have located the file to upload click the Open button. This window will close.
8. You are now back in the Manage Attachments - Excel Forum window.
9. Click the Upload button and wait until the file has uploaded.
10. Close the window and then click Submit.

[foxy_420]

Thank you very much. Here is the copy of my excel file.
Diabetes test.xlsx

You can ignore the rest of it for now. But eventually my plan is to automatically graph the A1C values for each corresponding month. I am not sure how I would go about doing that...suggestions are welcomed. But I know I shouldn't make this topic go to far off-thread.

[Norie]

I can see what the problem is.

In both column B and C you have text - 'nothing done' I think it says.

[foxy_420]

Is that for sure the problem? Because the fields that say "oct-2012" do appear to be dates.

I can count the fields that have A1C >= 7 ...even with the "never done" there. I just want to count the dates that are A1C >= 7 for the dates that have "oct" (or are in October)

Cells F14 and F15 have the codes i've tried to create...

[Norie]

I'm not 100% sure without looking at the actual file, but it's the only thing I can see that's not quite right.

Why not try yourself and replace 'never done' with 0?

[foxy_420]

The reason I don't replace never done with 0 is kind of confusing, but ill try to explain.

What I have done to get this data is i have exported this information from another program, however if a particular patient did not have the A1C test done it indicitates it by "never done". Therefore it is the other program that does this, and when I export it into a CSV file...then into Excel it has never done. I want to leave it that way because I need to eventually graph the patients that are "never done" (ie, did not have the test done) for each month.

Eventually, I need to find a way that will AUTOMATICALLY create a graph based on the A1C files for each month (ie. every month the graph will add data for that particular month)...I really have no idea how I am going to do that... suggestions are welcomed for that idea.

Hope that makes sense. But right now I am trying to do incremental steps...and trying to count how many times A1C value => 7 in "Oct" is my first step I think?!?!

[Norie]

Can you not just replace with 0 to see if the formula I posted works?

You can always change it back.

PS Have you tried attaching a workbook instead of a picture?

[foxy_420]

Yes I have attached a workbook a couple posts up

It is 'Diabetes_test"

[Norie]

Oops, sorry about that - thought the paper clip was referring to the picture.

[foxy_420]

No worries, let me know if looking at it helps at all. Thanks a bunch

[Norie]

Well I found out one thing - if you replace the text values with 0 the formula works.

Must you keep the text? Couldn't you use something in place of it that would still indicate 'not done'?

[foxy_420]

The formula didn't seem to work for me even after I put "never done" to 0. I have attached the newest version of it. The formula is in the F18 cell

Diabetes test.xlsx

[Norie]

Oops again, you need to exclude the header too.

=SUMPRODUCT(--(B3:B35>=7), --(MONTH(C3:C35) = 10))

[foxy_420]

okay great, thank you .

I guess there probably isn't a easy way to replace "never done" with 0 easily? For future reports they will all have "never done"... so that could be a bit problematic.

Is CTRL + F replace all the easiest way?

[Norie]

You could replace 'never done' with 0 using a couple of lines of code.

If you don't want to use code the Find>Replace All is probably the best way.