Array formula for count

[lowbjai]

Hi All,

I am looking for a formula which will do the following.

I have two columns
columnA: invoice receipt date
columnB: Invoice Base Date.

I want to find out how many time does the invoice receipt date - invoice base date = to some values.

eg. for invoice 1 i might have both dates equal but for invoice 2 there are 5 day difference. I also have a problem that because of system error, some field in invoice receive date are missing, so i will just ignore them and count for those which is not blank.

I have a table with numbers 0 to 20 which is the number of days they are difference by.

This formula is what I am trying to build on, I dont think this is right since its not giving me the right answer at all

={COUNT((IF((InvoiceReceiveDate-InvoiceBaseDate)=C11,1)*IF(InvoiceReceiveDate<>"",InvoiceReceiveDate))}

Hope you guys could help me out!

 

[Rick Rothstein]

Hi All,

I am looking for a formula which will do the following.

I have two columns
columnA: invoice receipt date
columnB: Invoice Base Date.

I want to find out how many time does the invoice receipt date - invoice base date = to some values.

Give this formula a try (row range assumed to be 2 thru 29 and value to test against is 5... adjust to match your actual setup)...

=SUMPRODUCT(1*(B2:B29-A2:A29=5))

Of course you could put the 5 in a cell and then use a reference to that cell in place of the 5 in the fomula.

 

[lowbjai]

Hi Rick,

Thanks for the reply, but when I try out your method, it comes up with #N/A error, I suspect because some of the data in the invoice receript date are blanks?

 

[Rick Rothstein]

Hi Rick,

Thanks for the reply, but when I try out your method, it comes up with #N/A error, I suspect because some of the data in the invoice receript date are blanks?

Blanks should not affect the workings of that formula. Did you keep the row ranges the same for both column references (that is a requirement)?