Countif or Sumproduct?

**Scotsman89** · 07-06-2011, 12:22 PM

Good afternoon all, I am having a bit of difficulty with a formula and hoping someone here can help me.

I have a spreadsheet where I want to count how many cases there are for each month so I have the following formula

=COUNTIF('DS DO Transfers'!A:A,"<"&I20)-COUNTIF('DS DO Transfers'!A:A,"<="&I19)

In column I20 and I19 I have the date range and this works fine.

I now want to count how many of these cases are completed within the same range.

So I can do this for the whole spreadsheet but I don’t know how to add in the date command.

So the formula to find the whole sheet is simply:

=COUNTIF('DS DO Transfers'!P:P,"Case Complete")

I believe I cant use countif if there is more than one criteria so would I need to use something like =Sumproduct?

Any help would be really appreciated as I am getting near giving up!

Cheers

**NBVC** · 07-06-2011, 12:39 PM

If you have XL2007 or later you can use COUNTIFS for multiple criteria, else yes Sumproduct.. but not with whole column references.

e.g

=SUMPRODUCT(--(P'DS DO Transfers'!$P$1:$P$100="Case Complete"),--('DS DO Transfers'!$A$1:$A$100<I2),--('DS DO Transfers'!$A$1:$A$100>I19))

**Scotsman89** · 07-06-2011, 01:01 PM

Originally Posted by NBVC

If you have XL2007 or later you can use COUNTIFS for multiple criteria, else yes Sumproduct.. but not with whole column references.

e.g

=SUMPRODUCT(--(P'DS DO Transfers'!$P$1:$P$100="Case Complete"),--('DS DO Transfers'!$A$1:$A$100<I2),--('DS DO Transfers'!$A$1:$A$100>I19))

That is fantastic, it worked with

=SUMPRODUCT(--('DS DO Transfers'!$P$1:$P$100="Case Complete"),--('DS DO Transfers'!$A$1:$A$100<I20),--('DS DO Transfers'!$A$1:$A$100>I19))

Thanks a lot for your help.

A final question if its ok, if i wanted to find the value of the cases were complete for the exact same range but in column Q I thought it would be the formula below but it does not work. Any ideas?

=SUMPRODUCT(--('DS DO Transfers'!$P$1:$P$100="Case Complete"),--('DS DO Transfers'!$A$1:$A$100<I20),--('DS DO Transfers'!$A$1:$A$100>I19)),'DS DO Transfers'!$Q1$:$Q100)

**NBVC** · 07-06-2011, 01:12 PM

Seems like it should work if the first one did... are you sure the values in Q are formatted as General or Number....

Try selecting column Q and go to data|text to columns and just click Finish.

Then format the column as General (or Number).

Does that work?

**Scotsman89** · 07-07-2011, 04:14 AM

Originally Posted by NBVC

Seems like it should work if the first one did... are you sure the values in Q are formatted as General or Number....

Try selecting column Q and go to data|text to columns and just click Finish.

Then format the column as General (or Number).

Does that work?

It says "the formula you typed contained an error" so i must have something a little wrong but cant see what it is! Any help would be greatly appreciated!

**NBVC** · 07-07-2011, 07:46 AM

Try:

=SUMPRODUCT(--('DS DO Transfers'!$P$1:$P$100="Case Complete"),--('DS DO Transfers'!$A$1:$A$100<I20),--('DS DO Transfers'!$A$1:$A$100>I19),'DS DO Transfers'!$Q$1:$Q$100)

**Scotsman89** · 07-08-2011, 05:46 AM

Originally Posted by NBVC

Try:

=SUMPRODUCT(--('DS DO Transfers'!$P$1:$P$100="Case Complete"),--('DS DO Transfers'!$A$1:$A$100<I20),--('DS DO Transfers'!$A$1:$A$100>I19),'DS DO Transfers'!$Q$1:$Q$100)

Brilliant it works perfectly! Thanks again for your help!