CODEWITHSUNDEEP
excel-formula
BruceWayne
BruceWayne

Reputation: 23283

CountIfs with Multiple Criteria?

I have a table like this:

Date Paid   Days Late   Date Paid   Days Late   Date Paid   Days Late   Date Paid   Days Late   Date Paid   Days Late   Date Paid   Days Late
        Utilities       Admin       Utilities       Admin       Utilities       Admin   
    Company January January January January February    February    February    February    March   March   March   March
    Wayne Enterprises   2/15/2016   5   10-Feb  0   3/11/2016   1           4/15/2016   5   4/25/2016   15
    Stark Industries    2/12/2016   3   2/8/2016    0   3/19/2016   10  3/8/2016    0   4/15/2016   5   4/1/2016    0

(I suggest though looking at the screenshot to see how it's laid out, as the pasting into here isn't very pretty, any tips? Here's a link to this on Google Spreadsheets).

How can I, in N4, create a formula that will count the number of months what have a late report (defined as any "Days Late" over 0). Obviously, I can do this:

=COUNTIFS(B1:M1,"Days Late",B4:M4,">0"), which returns 4.

For Wayne Enterprises, in March, both Utilities and Admin were paid late. However, this should only count as 1 month. How can I somehow add to my CountIfs() a statement that is like "if two values in the same month are greater than 0, treat as ONE month"?

I tried also doing something like:

=COUNTIFS(B1:M1,"Days Late",B4:M4,">0")/COUNTIFS(B1:M1,"Days Late",B4:M4,">0")+1

But that doesn't quite do the trick either. Thanks for any ideas. (Of course, if CountIfs() isn't the best way, I'm open to any other formulas! I have a sneaking suspicion SumProduct() might be an alternative) I'd prefer a formula solution over VBA, but if absolutely necessary, we can do a UDF perhaps.

Edit: I could create yet another helper column that compares a single month's Utilities and Admin, and if one or both are late, put 1, then just Countif() that column has 1 in it...but I'd rather not keep creating columns if I can help it, as I'll be doing this for 12 months.

Upvotes: 1

Views: 170

Answers (1)

Scott Craner
Scott Craner

Reputation: 152450

Use this Array formula:

=SUM(IF(($B4:$M4>0)*($B$1:$M$1="Days Late"),1/COUNTIFS($B$3:$M$3,$B$3:$M$3,$B4:$M4,">0",$B$1:$M$1,"Days Late")))

Being an array formula it must be confirmed with Ctrl-Shift-Enter when exiting edit mode. If done properly excel will put {} around the formula.

enter image description here

As per your new data use this:

=SUM(IF(($B5:$BI5>0)*($B$1:$BI$1="Days past 10th of Following Month"),1/COUNTIFS($B$3:$BI$3,$B$3:$BI$3,$B5:$BI5,">0",$B$1:$BI$1,"Days past 10th of Following Month")))

The helper row of months in row 4 is not needed.

EDIT #2

My bad I forgot that when using formulas that return "" will cause an error so lets put in a check for that:

=SUM(IF(($B5:$BI5<>"")*($B5:$BI5>0)*($B$1:$BI$1=$AS$1),1/COUNTIFS($B$3:$BI$3,$B$3:$BI$3,$B5:$BI5,">0",$B$1:$BI$1,$AS$1)))

Upvotes: 1

Related Questions