Using more combinations in countif function

[saru5133]

Hi,

I have uploaded the test file here.

https://rapidshare.com/files/439181260/Test.xlsx" onclick="window.open(this.href);return false;

I am trying to insert a countif formula in the sheet1 with data available in sheet2.

I already gave one countif formula with a combination of "Past and present"(filter drop down)

Now i would like to add the location also in this.

Can any one help.

please let me know if i need to explain more clearly.

[Claude]

Welcome to Eileen's Lounge!

The download is only available to rapidshare customers, please upload your test file here, thanks.

[saru5133]

Hello,

I have uploaded the file.
Please let me know if you are able to view it.

[Claude]

Thanks Saras, yes, I can view it. Alas, I'm no Excel expert, so we'll need to wait for some of them to be online...

[saru5133]

Sure Claude.

I guess we have chatting option in this site / forum.

[HansV]

Welcome to Eileen's Lounge!

I have attached a modified version of your workbook. Tou can select a location from the dropdown in K1.

I have also included a sheet with a pivot table that provides the same result without any formulas.

Test2.xlsx

[StuartR]

You should be able to do this using the SUMPRODUCT worksheet function.

For example a formula such as

Code: Select all

=SUMPRODUCT((Sheet2!$F:$F="Past"&$A1)*(Sheet2!$D:$D=B$2))

Will count how many rows in Sheet 2 have Past1 in column F and Admin in column D
You could add to this to give something like

Code: Select all

=SUMPRODUCT((Sheet2!$F:$F="Past"&$A1)*(Sheet2!$D:$D=B$2)*(Sheet2!$G:$G="Cat"))

To count only the rows that also have "Cat" in column G.

The formula will get pretty complicated because of the need to support "Past+Present" which will need a nested IF formulae to give the exact SUMPRODUCT formula you need.

Edited by StuartR to add
I see I have been beaten by Hans as usual, and that he has provided a much better answer too! Such is life.

[Claude]

I guess we have chatting option in this site / forum.

I'm afraid no, the idea of the Lounge is to provide solutions for everyone to see, a private chat wouldn't really assist others with the same queries / problems now, would it....

[saru5133]

Thanx Hans.

as per the test file if i remove the filter again, it is not giving me any values.
the values reflecting in the pivot table is not reflecting if apply any filters.
Help me with the formula which can give me the same result that of pivot table (if i apply any combination of filter as the pivot changes, the formula should also work in the same way.

[HansV]

The pivot table has two filter cells at the top: B1 and B2. These cells let you select a specific value or (All). For example, if you select (All) in cell B1, the effect is the same as selecting Past+Present. All this is done automatically by Excel, without needing any formulas.

If you want to do the same with COUNTIFS formulas, you have to add two IF formulas. For B4:

=COUNTIFS(Sheet2!$E$2:$E$11,$A4,Sheet2!$D$2:$D$11,B$2,Sheet2!$F$2:$F$11, IF($H$1="Past+Present","*",$H$1),Sheet2!$G$2:$G$11,IF($K$1="Cat+Mat","*",$K$1))

and copy this to the other cells with formulas. If you need to add more combinations, the formula will become ever longer...

[saru5133]

Hi,

I have uploaded the actual file i am working on.
could you help me apply the formula in this file and resolve the query please?

As per the uploaded file, i get the data from sheet based on the timeline (Past / present)
now i want the same combination to be applied for two more options (Location(column G), ordertype(Column B) of sheet2).

thanks in advance

[HansV]

Why do you have these concatenated codes in the "IN" and "OUT" columns? It would be much easier of the parts were in separate columns.

[saru5133]

That is how i designed the analysis report.... :))) of course as per the order

[saru5133]

becuase i want to track some information on timely basis.

[HansV]

See the attached version (I saved it as a .xlsm because it uses COUNTIFS and because it contains macros). I inserted a row to let the user select a location, and I don't use the formulas in column G.

Countif_Test.xlsm

In cell D14 etc., a single COUNTIFS formula is used. For example in D14:

=COUNTIFS(Sheet2!$B$2:$B$481,SUBSTITUTE($D$6,"D+O+I","*"),Sheet2!$G$2:$G$481, SUBSTITUTE($D$5,"Cat+Mat","*"),Sheet2!E$2:E$481,SUBSTITUTE($D$4,"Past+Present","*")&$C14&D$11)

and in E14:

=COUNTIFS(Sheet2!$B$2:$B$481,SUBSTITUTE($D$6,"D+O+I","*"),Sheet2!$G$2:$G$481, SUBSTITUTE($D$5,"Cat+Mat","*"),Sheet2!F$2:F$481,SUBSTITUTE($D$4,"Past+Present","*")&$C14&D$11)

These formulas can be filled down.

[saru5133]

ok. thats gr8.
But will this not work without macros?
Where can i see the macro coding used.

[HansV]

It works without any macros, but the workbook that you attached contains VBA code; this would have been lost if I had saved the workbook as a .xlsx file.

To see the code:
- Press Alt+F11 to activate the Visual Basic Editor.
- Expand Modules by clicking the + to the left of it.
- Double-click usefulFunction.

x437.png

The module contains some user-defined functions. If you don't need them, you can save the workbook as a .xlsx and ignore the warnings that you will lose the VBA code.

[saru5133]

Yes, i saved the file as .xls, and the formula still works perfect.

That was a gr8 help Hans....
you resolved my issue and answered all my queries with great patience....you are my solution box... :)))))).
Thanq once again.

[HansV]

You're welcome! Feel free to come back to Eileen's Lounge if you have more questions.

Just a small warning: if someone opens the .xls workbook in Excel 2003 (or an earlier version), the COUNTIFS formulas won't work.

[saru5133]

Sure, i would definitely like to reach out to experts like you any time.
May be this time i will come back with an access query.I am working on it.so need an expert like you again.... :)))

Thanx Hans