Advanced function using multiple Excel files.

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
 

Kevin@Radstock

New member
Joined
Oct 5, 2012
Messages
52
Reaction score
0
Points
0
Excel Version(s)
365
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
Joined
Nov 22, 2012
Messages
18
Reaction score
0
Points
0
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

New member
Joined
Oct 5, 2012
Messages
52
Reaction score
0
Points
0
Excel Version(s)
365
Hi yoshimura

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

yoshimura

New member
Joined
Nov 22, 2012
Messages
18
Reaction score
0
Points
0
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
Joined
Mar 21, 2011
Messages
1,940
Reaction score
0
Points
36
Excel Version(s)
O365
Try

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

yoshimura

New member
Joined
Nov 22, 2012
Messages
18
Reaction score
0
Points
0
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
Joined
Mar 21, 2011
Messages
1,940
Reaction score
0
Points
36
Excel Version(s)
O365
Try

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

Bob Phillips

Super Moderator
Staff member
Joined
Mar 21, 2011
Messages
1,940
Reaction score
0
Points
36
Excel Version(s)
O365
I misread what SUBSCR was

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

yoshimura

New member
Joined
Nov 22, 2012
Messages
18
Reaction score
0
Points
0
How should one go about counting the number of postal codes (say that contain "M*") between specified dates?
 

yoshimura

New member
Joined
Nov 22, 2012
Messages
18
Reaction score
0
Points
0
=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
Joined
Mar 27, 2012
Messages
40
Reaction score
0
Points
0
You can always try to use the <> when needing to exclude info
 

yoshimura

New member
Joined
Nov 22, 2012
Messages
18
Reaction score
0
Points
0
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
Joined
Mar 21, 2011
Messages
1,940
Reaction score
0
Points
36
Excel Version(s)
O365
I think your problem is because Form-D.xlsx is set to 1904 date system.
 

yoshimura

New member
Joined
Nov 22, 2012
Messages
18
Reaction score
0
Points
0
Yep, I set it to 1904 date system and it shows the same erroneous amount.
The total should be about 189.
 

yoshimura

New member
Joined
Nov 22, 2012
Messages
18
Reaction score
0
Points
0
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
Joined
Mar 21, 2011
Messages
1,940
Reaction score
0
Points
36
Excel Version(s)
O365
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
Joined
Nov 22, 2012
Messages
18
Reaction score
0
Points
0
You're absolutely right Bob!

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

Thanks :clap2:
 
Top