Array formula for count

lowbjai

New Member
Joined
Mar 12, 2013
Messages
7
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!
 

Excel Facts

Round to nearest half hour?
Use =MROUND(A2,"0:30") to round to nearest half hour. Use =CEILING(A2,"0:30") to round to next half hour.
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.
 
Upvote 0
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?:confused:
 
Upvote 0
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?:confused:
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)?
 
Upvote 0

Forum statistics

Threads
1,213,561
Messages
6,114,315
Members
448,564
Latest member
ED38

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.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

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

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

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
Back
Top