Advanced function using multiple Excel files.

[yoshimura]

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

[Kevin@Radstock]

Hi yoshimura

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

Kevin

[yoshimura]

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

[Kevin@Radstock]

Hi yoshimura

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

[yoshimura]

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]

Try

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

[yoshimura]

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]

Try

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

[yoshimura]

This is what it says in the cell:

#NAME?

[Bob Phillips]

I misread what SUBSCR was

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