Array formula for count

lowbjai

New Member
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

Hope you guys could help me out!

Excel Facts

VLOOKUP to Left?
Use =VLOOKUP(A2,CHOOSE({1,2},\$Z\$1:\$Z\$99,\$Y\$1:\$Y\$99),2,False) to lookup Y values to left of Z values.

Rick Rothstein

MrExcel MVP
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

New Member
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

MrExcel MVP
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)?

Replies
1
Views
136
Replies
12
Views
302
Replies
4
Views
99
Replies
0
Views
337
Replies
1
Views
134

1,195,598
Messages
6,010,644
Members
441,558
Latest member
lambierules

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back