Need help - Count unique value that matches given condition

use Pivot table to achieve this.

Is there a way to calculate using formula? we would like to automate the report so that when raw data are pasted in the first table, the number will just come out rather than having a person using pivot table to find out the number and type it in the second table.

Table 1 is from a sheet called Oct15
Table 2 is from a sheet called Consolidation

Here, try this:

Formula:

Please Login or Register  to view this content.

Confirmed with ctrl+shift+enter (not just enter) where A3 is criteria in current sheet.

Hi zbor,

I tried to replace with the correct address but it returns the value = 0..., even when I hit ctrl shift enter

I think I replace it wrong...

I've attached the original file, please could you take a quick look to see how I should replace it.

The consolidation table is from A53:N58 in sheet consolidation.
The data for B55 in sheet consolidation will be taken from sheet Oct15.

Here is the original file
https://www.mediafire.com/?eb0tvnccqgw12ey

I apologize for being such a bother.

With this formula:

Formula:

Please Login or Register  to view this content.

this is a result I've got:

Please Login or Register  to view this content.

Wow zbor, I got it. You're my hero.

What's the difference between enter and ctrl shift enter? Why does pressing them giving different results in this case?

It's called array formula.
An array formula is a formula that works with an array, or series, of data values rather than a single data value.

To enter a formula as an array formula, type the formula in the cell and press the CTRL SHIFT and ENTER keys at the same time rather then just ENTER.
You must do this the first time you enter the formula and whenever you edit the formula later.
If you do this properly, Excel will display the formula enclosed in curly braces { }. You do not type in the braces -- Excel will display them automatically.
If you neglect to enter the formula with CTRL SHIFT ENTER, the formula may return a #VALUE error or return an incorrect result.

I have another question, not related to unique value though.

If I have a table like this

Capture.PNG

If I need to count how many lines in column G that contain the word "ipad" or "tablet" or "android" (if it contains two words as in line 2, count as 1) what should I put for formula?

Please open new thread for new question.
You will also get answer sooner.