Solved: Ignore Countif If Cell Contains a Value

[mattster1010]

Afternoon All,

I am using the below formula to count the number of queries between two date ranges.


=COUNTIF('Open NHH Queries'!H:H,"<="&DATE(2009,1,31))-COUNTIF('Open NHH Queries'!H:H,"<"&DATE(2009,1,1))

I need to adapt it slighty and get the formula to also lookup column G:G and 'not' count the query if there is a value in column G:G. Any idea's how I could adapt the formula?

Regards,
Mattster

[mancubus]

maybe...



=SUMPRODUCT(--('Open NHH Queries'!H:H<=date(2009,1,31)),
--('Open NHH Queries'!H:H>=date(2010,1,1)),
--('Open NHH Queries'!G:G=""))

ps: i recommend you use a specific range (H2:H5000) instead of whole column (H:H)

[mattster1010]

Thank mancubus,

Ive tried the below formula but it seems to be returning an extra total count than the extra figure. For instance queries between Jan 1 - Jan 31 are 1, the formula is returning 2. I have checked with other date ranges and the outcome is the same. Any idea's?

[mattster1010]

Plese forget the last post. The formula works as you originally posted.

Thank you for your help.

[mancubus]

glad it helped.

correction of a typo in the second date.



=SUMPRODUCT(--('Open NHH Queries'!H:H<=date(2009,1,31)),
--('Open NHH Queries'!H:H>=date(2009,1,1)),
--('Open NHH Queries'!G:G=""))