LinkBack URL
About LinkBacks
Raw data on sheet 1
Kindly note I don't want it to count the credit notes ,
only invoices
Nor I want a pivot , I am looking for formula or macro
You will definitely need a helper to convert date into
month i guess
Raw data on sheet 1
Kindly note I don't want it to count the credit notes ,
only invoices
Nor I want a pivot , I am looking for formula or macro
You will definitely need a helper to convert date into
month i guess
In B2
=SUMPRODUCT((mak1!$G$2:$G$31=$A2)*(TEXT(mak1!$C$2:$C$31,"mmm")=B$1)*(mak1!$D$2:$D$31="Invoice"))
Copy down and across
Life's a spreadsheet, Excel!
Say thanks, Click *
ACE_XL you are really and ACE ,
you did not even even use helper
Imagine the results of 10,000 row , wow
Use
=SUMPRODUCT(--(mak1!$D$2:$D$31="Invoice"),--(TEXT(mak1!$C$2:$C$31,"mmm")=B$1),--(mak1!$G$2:$G$31=$A2))
and copy down and across
Its slow at 25000 rows of data
Some who can assist with a macro ,
I have collect the formula for smaller
range say 2500 rows
Re: Invoice count per head per month extract
Here is any another alternative..
Using an helper column with COUNTIFS will be fastest..
Put this in Column I of sheet1 and drag down ( as Helper column )
Formula:
Please Login or Register to view this content.
Now in sheet2
PutFormula:Please Login or Register to view this content.
Drag horizontally and vertically..
NOTES:-
COUNTIFS are very much efficient in handling blanks cells..
Regards,
Regards,
Vikas Gautam
Excel-buzz.blogspot.com
Excel is not a matter of Experience, its a matter of Application.
Say Thanks, Click * Add Reputation
Since, all your invoice values come under dr side, can we use sumifs whichever is lower than 0,
Please confirm do you have any value in credit side apart from credit note (like sales return..)
If no value comes under credit side apart from credit note we can reduce few criteria
☚ Click ★ just below left if it helps, Boo?ath?
VIKAS your helper case is kill , it did 20,000 rows at less than 5 seconds
I have written in my diary about HELPER , they really do the magic
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
Register To Reply
Bookmarks