COUNTIF function question...

[gordonrmac]

Greetings,

So I have a spreadsheet where we enter customer data and then we use a
COUNTIF on another sheet to count several categories (Sales Rep, type,
etc). What I am trying to find out is if I can add up the sales per
rep, whose names are entered in column G, but only count them if there
is a certain entry in column D. Column D has 6 options, and if 2 of
them are selected, I want the sales rep's numbers to be excluded from
the data collection. Does this make sense?

Thoughts? Suggestions?

I know what I want the function to say, just don't know how I should
program it:

"Count the number of times rep name John Doe shows up on column G on
sheet MONTH except if column D is = X or Y"

Can this even be done?

 

[Ron Rosenfeld]

Greetings,

So I have a spreadsheet where we enter customer data and then we use a
COUNTIF on another sheet to count several categories (Sales Rep, type,
etc). What I am trying to find out is if I can add up the sales per
rep, whose names are entered in column G, but only count them if there
is a certain entry in column D. Column D has 6 options, and if 2 of
them are selected, I want the sales rep's numbers to be excluded from
the data collection. Does this make sense?

Thoughts? Suggestions?

I know what I want the function to say, just don't know how I should
program it:

"Count the number of times rep name John Doe shows up on column G on
sheet MONTH except if column D is = X or Y"

Can this even be done?

Something like:

=SUMPRODUCT((rep="John Doe")*(type<>"X")*(type<>"Y"))




--ron

 

[Bill Ridgeway]

Have you tried pivot tables?

Regards.

Bill Ridgeway
Computer Solutions

 

[Gord]

Don't really know how they work, but I will look into it, thanks for
the suggestion.

 

[Gord]

So do you think I could replace the current =COUNTIF formula with this
one? Going to try it tomorrow...

 

[Ron Rosenfeld]

So do you think I could replace the current =COUNTIF formula with this
one? Going to try it tomorrow...

Let me know what happens.


--ron

 

[Bill Ridgeway]

Gord wrote <Don't really know how they [pivot tables] work>

Think of Pivot tables as a way of abstracting and analysing data. Go to
<Data> <Pivot table and pivot chart report> and follow the prompts. From the
<pivot table field list> select and drag the fields into the template. You
will need to experiment a little here to get it right but once you have
constructed the pivot table you can drag the data fields around until you
get it right or it is easy to start again.

Nothing ventured nothing gained!

Regards.

Bill Ridgeway
Computer Solutions

 

[Gord]

Bill,

Pivot tables seem to be the way to go - just trying to get them to
layout how i want them.

I notice they update everytime the sheet opens. Is there anyway to get
them to update automatically after new data is entered?

As well, what is this going to do to the file size? I would be looking
at quite a few tables, but as of now a full sheet is 2 MB, and we need
to get that file size down....

 

[Gord]

Actually, taking a different track with this one since Pivot tables,
while handy, made thefile size jump by quite a bit.

I am going to make a hidden column with an IF statement. So the idea I
want now is:

=IF(D1= X Y or Z,F1,"")

By rights, this should check if the value of cell D1 is = X, Y or Z,
and if it is, take the value from cell F1 (rep name), and if not, leave
it blank. Then I can just redirect my count if to this column.

Problem is i cannot get the syntax for multiple logicals for the D1=
right. Ideas?

 

[Guest]

I might have a solution to the file size issue regarding Pivot Tables.

Pivot Tables create a hidden COPY of the data being pivoted.

One of the common confusions with Pivot Tables is the option to base the
Pivot Table on another Pivot Table. That doesn't mean that you will only be
able to see the same (or less) data than the base Pivot. It means that it
will share the same copy of the data as the original Pivot Table.

Consequently, if you are creating multiple Pivot Tables that are based on
the same data and you DO NOT choose to base them all on the same Pivot Table,
you create a new copy of the base data for each Pivot Table. If there is a
lot of source data, the workbook will become huge.

Of course, if your Pivot Tables are all based on different data sources,
then these comments are irrelevent.

 

[jay]

Sounds like a job for the OR function?
if( or( D1 = "X", D1="Y", D1= "Z" ), F1, "")
*** spaces added for readibility***
-if the OR function returns TRUE, then use the value- else leave it
blank.
(wow, something I could answer!)
cheers
Jay