# Advanced function using multiple Excel files.

#### yoshimura

##### New member
Hello,

Total Excel newbie here. I've figured out some simple stuff, but I'm running into difficulty with more advanced functions.

Add the total number of cells in the 'POSTAL' column that begin with 'M' within the 'SUBSCR' file (it's a separate Excel file, Sheet1).

Regards,
Yoshi

##### New member
Hi yoshimura

Try the COUNTIF function. For example if you had a series of data in A1:A30: =COUNTIF(A1:A30,"M*")

Kevin

#### yoshimura

##### New member
Hello Kevin,

That helped!

In the COUNTIF function example (=COUNTIF(A1:A30,"M*")), I would like to know how to include and exclude.

How can I include Cells that begin with "M*" and "L*"?

With the above conditions true (include cells that begin with "M*" and "L*"), what if I want to exclude cells?
Here are two separate scenarios:
1) Exclude cells containing "L8*"
2) Exclude all other letters that are not "M* or L*"

Eric

##### New member
Hi yoshimura

=SUM(COUNTIF(A1:A30,{"M*","L*"}) array formula. CTRL + SHIFT + ENTER. Not just enter.

#### yoshimura

##### New member
Hello Kevin,

The =SUM(COUNTIF) array forumla was enough for me to do all the calculations.

There is just one problem: When counting postal codes "P*", it includes the header "POSTAL".
What can I do to solve this?

#### Bob Phillips

##### Super Moderator
Staff member
Try

=SUMPRODUCT(COUNTIF(A1:A30,{"M*","L*","P*"})-COUNTIF(A1:A30,"POSTAL")

#### yoshimura

##### New member
Hmmm, not working yet.

I have to specify that it's taking the range from the sheet "SUBSCR".

I tried:

=SUMPRODUCT(COUNTIF(SUBSCR!A:A,{"M*","L*","P*"})-COUNTIF(SUBSCR!,"POSTAL")
and
=SUMPRODUCT(COUNTIF(SUBSCR!A:A,{"M*","L*","P*"})-COUNTIF(SUBSCR!,A:A,{"POSTAL"})

#### Bob Phillips

##### Super Moderator
Staff member
Try

=SUMPRODUCT(COUNTIF(SUBSCR,{"M*","L*","P*"})-COUNTIF(SUBSCR,"POSTAL")

#### yoshimura

##### New member
This is what it says in the cell:
 #NAME?

#### Bob Phillips

##### Super Moderator
Staff member
I misread what SUBSCR was

=SUMPRODUCT(COUNTIF(SUBSCR!A1:A30,{"M*","L*","P*"})-COUNTIF(SUBSCR!A1:A30,"POSTAL")

#### yoshimura

##### New member
Thanks Bob, that worked.

#### yoshimura

##### New member
How should one go about counting the number of postal codes (say that contain "M*") between specified dates?

#### yoshimura

##### New member
=COUNTIFS('[SUBSCR.xls]SUBSCR'!\$B:\$B,">="&B8,'[SUBSCR.xls]SUBSCR'!\$A:\$A,"M*")

I tried the above formula to no avail. B8= 11/27/11

#### MZING81

##### New member
You can always try to use the <> when needing to exclude info

#### yoshimura

##### New member
Hey MZING81,

I'm not sure how I can use that.
I've attached the two workbooks for everyone to checkout. View attachment Archive.zip

The correct number that the formula in C12 is supposed to output is 191.

Regards,
Yoshi

#### Bob Phillips

##### Super Moderator
Staff member
I think your problem is because Form-D.xlsx is set to 1904 date system.

#### yoshimura

##### New member
Yep, I set it to 1904 date system and it shows the same erroneous amount.
The total should be about 189.

#### yoshimura

##### New member
It should be counting for all dates great or equal to B8 (11/27/11).

I think Excel is including dates that have 11 at the beginning of the date, not just at the end.

#### Bob Phillips

##### Super Moderator
Staff member
No, it is including anything after 27th Nov 2007, four years earlier than your date in B8, because of the 1904 date system. You need to uncheck that option in Excel options.

Last edited:

#### yoshimura

##### New member
You're absolutely right Bob!

I did not notice the date change to 2007… then I had another look.

Thanks :clap2: