COUNTIF and INDIRECT

**Simon Gahan** · 08-20-2012, 11:48 AM

Hi All,

Having a bit of a problem with one of the longer formulas I am using for a new template that I am building.

Long story short I'm looking to return a value based on 3 different criteria from three different ranges that are determined by the days of the month. e.g. I want to find out what customer dealt with what client, the nature of their business and I want it to be determined by the day in question.

So I have written the following and it is returning 0.

=COUNT(IF((INDIRECT("Input!H"&L6&":H"&L7)=G$1)*(INDIRECT("Input!I"&L6&":I"&L7)=$A4)*(INDIRECT("Input!J"&L6&":J"&L7)=$A$3),(INDIRECT("Input!K"&L6&":K"&L7))))

I have a formula that I am already using

=COUNT(IF((Input!$I$2:$I$1982=Consolidated!$A4)*(Input!$H$2:$H$1978=Consolidated!F$1)*(Input!$J$2:$J$1977=Consolidated!$A$3),Input!$K$2:$K$1985))

which is returning the value that I want, but it is the total for the month rather than the days in question. Hence the attempted INDIRECT in the first formula above.

So if anyone can help me I would appreciate greatly. I'm sure its something relatively simple, but I cant see it.

Guys I appreciate any help you can give and if you need me to provide more colour on the above formulas to help you help me, then I will! :-)

All the best

Simon

**NBVC** · 08-20-2012, 12:17 PM

Maybe a sample workbook might help us to better understand?

**Simon Gahan** · 08-20-2012, 04:26 PM

Hi,

So I have attached a rudimentary representation of the sheet I have been working on in the office.

This sheet has the basics of what I'm looking for, i.e. I want to know how many time client a was rejected by bank b on the 14th. My aim is to achieve this by using countif with three variables, and an indirect linked to the row start and end, dictated by the date in question.

The formula I tried is up above, but it keeps returning me a zero even though I know that there is a value to be returned.

Again any help would be much appreciated.

Thanks,

Simon

**NBVC** · 08-20-2012, 04:55 PM

Does this work?

=COUNTIFS($C$4:$C$20,J$9,$D$4:$D$20,$I11,$E$4:$E$20,"Rejected",$F$4:$F$20,DAY($N$8))

in J11, copied down and across

---------- Post added at 04:55 PM ---------- Previous post was at 04:47 PM ----------

Actually, I just noticed you have a table for Filled and one for Rejected.

You can then use this in J11:

=COUNTIFS($C$4:$C$20,J$9,$D$4:$D$20,$I11,$E$4:$E$20,$I$10,$F$4:$F$20,DAY($N$8))

and this in J16:

=COUNTIFS($C$4:$C$20,J$9,$D$4:$D$20,$I16,$E$4:$E$20,$I$15,$F$4:$F$20,DAY($N$8))

watch for extra spaces in your cells... they will cause you to get 0 results....

**Simon Gahan** · 08-21-2012, 05:28 AM

Hi,

I have never used the COUNTIFS formula before so I just want to confirm your methodology here.

Your using the COUNTIFS to consolidate the formula I have above and then use the last COUNTIF with the DAY to replace the INDIRECT function?

Simon