COUNTIF with more than one logical argument. COUNTAND perhaps?

**spawn2048** · 03-04-2012, 01:04 PM

Hello

I am struggling with a problem where I need to count the instances of something based on 2 logical arguments. I'm writing a spreadsheet to keep track of invoices and I want to monitor which clients regularly pay late and by how much.

I would like a COUNTIF function to count how many times client A pays an invoice >0 days past the due date. So I have columns containing client name (A), date invoice is due (B), date invoice is paid (C) and difference between the two dates (D). I would like to count how many times client A appears in column A with column D >0. Essentialy =COUNTIF(A:A,"Client A" AND D:D,>0) but of course this syntax is not correct.

Does anyone have any suggestions on how I can achieve a COUNTIF with 2 logical arguments?

Thanks

**spawn2048** · 03-04-2012, 01:08 PM

...also...does a MAXIF function exist? For example find the maximum value in column D where column A is equal to "Client A"

Thanks

**daddylonglegs** · 03-04-2012, 01:18 PM

Try SUMPRODUCT for a multi-condition count, although you can't use the whole column as you can with COUNTIF. For example

=SUMPRODUCT((A2:A100="Client A")*(D2:D100>0))

and for MAX with conditions try an "array formula"

=MAX(IF(A2:A100="Client A",D2:D100))

the latter confirmed with CTRL+SHIFT+ENTER

**spawn2048** · 03-04-2012, 02:00 PM

Thank you! The SUMPRODUCT works with a little bit of tweaking.

I cant get the conditional MAX to work. Not sure what you mean by confirm with ctrl shift and enter. Working on a Mac so don't know if that affects anything to do with the ctrl/apple key.

**daddylonglegs** · 03-04-2012, 02:14 PM

On a Mac I believe you need Command+Return instead. If done correctly you get curly braces like { and } around the formula. As an alternative you can add an INDEX function then the formula can be entered like any other, i.e.

=MAX(INDEX((A2:A100="Client A")*D2:D100,0))

No text allowed in D2:D100