I have this formula but I have discovered that it is picking up rows that I do not want. How for example could I elimate a row if one of the columns contain the word "WRITEOFF" and "ADD NEW"?
Please Login or Register to view this content.
I have this formula but I have discovered that it is picking up rows that I do not want. How for example could I elimate a row if one of the columns contain the word "WRITEOFF" and "ADD NEW"?
Please Login or Register to view this content.
Hi,
I'd need a small sample file to be ore precise. Let's assume WRITEOFF could be part of contents in column K.
In COUNTIFS you could add a new parameter.
Something like
...Transactions!$K$4:$K$475,"<>"&"*WRITEOFF*"
In SMALL to exclude WRITEOFF
...IF(ISERROR(SEARCH("WRITEOFF";Transactions!$K$4:$K$475))...
Regards
Last edited by canapone; 03-20-2017 at 01:27 PM.
-----------------------------------------------------
At Excelforum, you can say "Thank you!" by clicking the star icon ("Add Reputation") below the post.
Please, mark your thread [SOLVED] if you received your answer.
----
double message: sorry
---------
Last edited by canapone; 03-21-2017 at 01:52 AM.
Hi
to count excluding ADD NEW and WRITEOFF
array enteredPlease Login or Register to view this content.
In order to get the smallest number from ROW($B$4:$B$474)-ROW($B$4)+1) excluding strings in J4:J474 containing WTRITEOFF or ADDNEW
Array enteredPlease Login or Register to view this content.
To attach a sample file here some instructions.
http://www.excelforum.com/faq.php?do...l&titlesonly=0
There are for sure better solutions on the way
Regards
Last edited by canapone; 03-21-2017 at 01:57 AM.
ok here is a sample file. The only items I want to appear in the ACCT sheet are obviously anything filtered by the month and year and just ITEMS that say "ALFA DENT" and "MAIN"
https://www.excelforum.com/attachmen...1&d=1490039206
Try this modification of the array entered formula that is in B7 with this array entered formula:*Array entered formulas are activated by pressing CTRL+SHIFT+ENTER to activate the array, not just ENTER. You will know the array is active when you see curly braces { } appear around your formula. If you do not CTRL+SHIFT+ENTER you will get an error or a clearly incorrect answer.Formula:Please Login or Register to view this content.
Let us know if you have any questions.
Consider taking the time to add to the reputation of everybody that has taken the time to respond to your query.
I can only assume you tested this formula with the attached file? I enter the formula and it produces a blank result. The logic was to find Alfa-Dent or Main and populate the table in the sheet ACCT dynamically. THerefore the only values that would show are those specified in the criteria and not the totals on the bottom of the Transactions Sheet for example.
Hi again
Excel 2010 users (and later version) can exploit AGGREGATE
In I7 to be copied below
Array status not requested. Just enter to work.Please Login or Register to view this content.
In B5 you could adjust COUNTIFS as
"*ALFA-DENT*" asks to count strings containing ALFA-DENT: maybe asteriks (*) are not necessary.Please Login or Register to view this content.
Please refer to the attachment: I've translated the formulas from a PC with Italian settings.
Hope it's a little help
Last edited by canapone; 03-21-2017 at 01:55 AM.
This looks great. Many Thanks
I will play around with it and come back to you if I have any questions.
If you get bored maybe you would take a look at my formula in F4 on Main Stock and tell me if this could be made more simple or do I pretty much have it correct?
Ciao,
I'll take a look.
Greetings from Firenze
Hi, I'm not able to integrate C4 as last choice into segment
{"ALFA-DENT"."WRITEOFF ALFA"."TRANSFER TO MAIN"."STOCK ADJUST MINUS AD"."STOCK ADJUST PLUS AD"."TRANSFER TO ALFA"}
I'm sure you have good reasons for using INDIRECT: I do not have the complete picture of your job.
I'm using a SUMIF instead of
VLOOKUP($C4,'STOCK HISTORY MAIN'!F:G,2,FALSE)
Hope it helpsPlease Login or Register to view this content.
Indeed I did and the attached is what I get.I can only assume you tested this formula with the attached file?
Let us know if you have any questions.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Bookmarks