Advanced function using multiple Excel files.

Y

yoshimura

New member
Joined
Nov 22, 2012
Messages
18
Reaction score
0
Points
0
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
 
Hi yoshimura

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

Kevin
 
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
 
Hi yoshimura

=SUM(COUNTIF(A1:A30,{"M*","L*"}) array formula. CTRL + SHIFT + ENTER. Not just enter.
 
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?
 
Try

=SUMPRODUCT(COUNTIF(A1:A30,{"M*","L*","P*"})-COUNTIF(A1:A30,"POSTAL")
 
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"})
 
Try

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

=SUMPRODUCT(COUNTIF(SUBSCR!A1:A30,{"M*","L*","P*"})-COUNTIF(SUBSCR!A1:A30,"POSTAL")
 
How should one go about counting the number of postal codes (say that contain "M*") between specified dates?
 
=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
 
You can always try to use the <> when needing to exclude info
 
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
 
I think your problem is because Form-D.xlsx is set to 1904 date system.
 
Yep, I set it to 1904 date system and it shows the same erroneous amount.
The total should be about 189.
 
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.
 
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:
You're absolutely right Bob!

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

Thanks :clap2:
 
You must log in or register to reply here.
Back
Top