CODEWITHSUNDEEP
excelexcel-formula
Edward Ko
Edward Ko

Reputation: 55

Count rows with multiple parameters in an Array formula

I want a count of all rows that are in the month of June and have NOT paid.

I have data as follows:

Type    Date        Invoice #   Customer    Memo    Date Due    Amt Chrg
Invoice  6/1/2018   1234         Name123             PAID        32.50
Invoice  6/7/2018   1235         Name12              7/22/18     12.30
Invoice  6/3/2018   1236         Name1               PAID        52.82
Invoice  7/1/2018   1237         Name                7/22/18     631.50
Invoice  8/1/2018   1238         Name213             PAID        236.20
Invoice  7/1/2018   1239         Name223             PAID        3.10
Invoice  7/5/2018   1240         Name323             PAID        16.60

My formula to calculate the count is below. Excel outputs: total rows - the count of invoices that are in June and HAVE NOT paid. 

{=COUNT(IF((MONTH(ARRegister!$B$2:$B$1275)=6)*(ARRegister!$F$2:$F$1275<>"Paid"),ARRegister!$A$2:$A$1275,0))}

How do I correct my formula so the count is output rather than total rows minus my count?

Upvotes: 0

Views: 81

Answers (2)

J.Mapz
J.Mapz

Reputation: 511

Assuming Date is in column B and Date Due is in column F, consider the following formula: =SUMPRODUCT(--(MONTH(B2:B1275)=6)*(F2:F1275<>"Paid"))

SUMPRODUCT(), as the name goes, "sums" the "product" of arrays within it. So basically, it works with arrays, but doesn't need to be returned using the CONTROL+SHIFT+ENTER combination.

The 'double negative' "--" is a trick to change TRUE/FALSE values into 1/0, which is then multiplied together and summed up.

Upvotes: 3

Dan Donoghue
Dan Donoghue

Reputation: 6206

Does it have to be an array formula? You could count the rows in month 6 then minus the ones which are month 6 and paid like so:

=COUNTIF(B:B,6)-COUNTIFS(B:B,6,F:F,"Paid")

Upvotes: -1

Related Questions