Using COUNTIF in 2003

[tinainsuffolk]

Hi all, Hoping someone can point me in the right direction.

This is my current formula

=COUNTIF(Duty!C:C,">=1-jan-2011")-COUNTIF(Duty!C:C,">=31-jan-2011")-COUNTIF(Duty!F:F,"=<""11")+COUNTIF(Duty!J:J,"IA")

This is wrong - When I try to run it for Feb or March, it is still counting the number of IAs in total – not just for that particular month

I want it to count the number of IAs in column J but only if the month is January 2011

Can anyone help?

Tina

[royUK]

Your criteria needs changing for each month

To best describe or illustrate your problem you would be better off attaching a dummy workbook, the workbook should contain the same structure and some dummy data of the same type as the type you have in your real workbook - so, if a cell contains numbers & letters in this format abc-123 then that should be reflected in the dummy workbook.

If needed supply a before and after sheet in the workbook so the person helping you can see what you are trying to achieve.

Doing this will ensure you get the result you need!

[daddylonglegs]

Hello Tina,

I don't think you can do that with COUNTIF functions, try using SUMPRODUCT, e.g.

=SUMPRODUCT((TEXT(Duty!C2:C1000,"mmm-yy")="jan-11")*(Duty!F2:F1000<=11)*(Duty!J2:J1000="IA"))

Change the ranges if you need to go beyond row 1000, but you can't use the whole column

[tinainsuffolk]

Thank you for your responses.

Having looked at the worksheet again, and added your suggestions, I have one that works and one that does not !

I want to count referrals coming in to my Duty sheet by month, for the stats sheet. I then want to count how many referrals had the outcome of IA.

For the first column, to simply count the referrals, I have tried:-

=SUMPRODUCT((TEXT(Duty!$A2:$A1000,"mmm-yy")="jan-11"))
and
=COUNTIF(Duty!A:A,">=1-jan-2011") - COUNTIF(Duty!A:A,">=31-jan-2011")
=COUNTIF(Duty!A:A,">=1-feb-2011") - COUNTIF(Duty!A:A,">=28-feb-2011")
etc etc

Only the Countif formula works. The Sumproduct doesnt on this particular one

However, the calculate the IAs per month, I tried:

=SUMPRODUCT((TEXT(Duty!$A2:$A1000,"mmm-yy")="jan-11")*(Duty!$J2:$J1000="IA"))
=SUMPRODUCT((TEXT(Duty!$A3:$A1001,"mmm-yy")="feb-11")*(Duty!$J3:$J1001="IA"))
etc etc,
and it works.

Should I leave the two columns of formulas different or would it be better to try and make them alike?

Tina

[daddylonglegs]

=COUNTIF(Duty!A:A,">=1-jan-2011") - COUNTIF(Duty!A:A,">=31-jan-2011")

Be careful with this, if you count all dates >= first of the month then subtract those >= the last of the month then you aren't including the last day of the month, the second >= should be just >, i.e.

=COUNTIF(Duty!A:A,">=1-jan-2011") - COUNTIF(Duty!A:A,">31-jan-2011")

Using two COUNTIF functions is a good approach for counting between two dates, there's no reason to change it. When you have another criterion, though, you'll need to use SUMPRODUCT or similar.

In my suggested SUMPRODUCT formulas with more than one condition the * "co-erces" the TRUE/FALSE values returned, if you have just a single condition you need a co-ercer, e.g.

=SUMPRODUCT((TEXT(Duty!$A2:$A1000,"mmm-yy")="jan-11")*1)

[tinainsuffolk]

Well I am so grateful for the help I have received. Yes, thanks Daddylonglegs, it now all works and I am very relieved and happy :-)


Tina