Need help with COUNTIFS and ROWS / SMALL

[calanon]

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.

[canapone]

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

[calanon]

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

Send me your email and I will send you the file

[calanon]

Hi,

In SMALL to exclude WRITEOFF

...IF(ISERROR(SEARCH("WRITEOFF";Transactions!$K$4:$K$475))...

I cant see how to incorporate this into SMALL. The column just for reference is J. In the Cells C4 and D4 are the month and year inside a drop down list.

[canapone]

----

double message: sorry

---------

[canapone]

Hi

to count excluding ADD NEW and WRITEOFF

Please Login or Register  to view this content.

array entered


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

Please Login or Register  to view this content.

Array entered

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

[calanon]

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

[JeteMc]

Try this modification of the array entered formula that is in B7 with this array entered formula:

Please Login or Register  to view this content.

*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.
Let us know if you have any questions.

[calanon]

Try this modification of the array entered formula that is in B7 with this array entered formula:

Please Login or Register  to view this content.

*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.
Let us know if you have any questions.

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.

[canapone]

Hi again

Excel 2010 users (and later version) can exploit AGGREGATE

In I7 to be copied below

Please Login or Register  to view this content.

Array status not requested. Just enter to work.

In B5 you could adjust COUNTIFS as

Please Login or Register  to view this content.

"*ALFA-DENT*" asks to count strings containing ALFA-DENT: maybe asteriks (*) are not necessary.

Please refer to the attachment: I've translated the formulas from a PC with Italian settings.

Hope it's a little help

[calanon]

This looks great. Many Thanks

I will play around with it and come back to you if I have any questions.

[calanon]

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?

[canapone]

Ciao,

I'll take a look.

Greetings from Firenze

[canapone]

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)

Please Login or Register  to view this content.

Hope it helps

[JeteMc]

I can only assume you tested this formula with the attached file?

Indeed I did and the attached is what I get.
Let us know if you have any questions.