counting based on criteria in another column

  • Thread starter Thread starter SWhitgob
  • Start date Start date
S

SWhitgob

Hi everyone! I think this will probably be an easy question for some of
you, but I have been trying for hours to come up with something!

I have a spreadsheet with two columns that I need to count the
occurence of "x" in column B, but only if column A is equal to
something specifc.

I need it to be exactly how the Sumif function works, but I need it to
count instead, because it is looking at x's, and not #'s.

If I change my x's to be 1's, the sumif function would correctly
calculate what I need. (Hopefully that makes what I am trying to say
easier to understand.)


Thanks in advance!
Shalon
 
Hi Shalon!

=SUMPRODUCT(--(A5:A250=whatever something specific means),-
-(B5:B250="X"))

Biff
 
Hi Biff!

Thanks so much for the formula, but it only gives me answers of zero!

I may not have explained it well first, so let me try again.

In further detail...

So column A lists my store numbers (ranging from 1-47, but some numbers
show up more than once.) This particular worksheet has 166 entries, but
the numbers are still only 1-47...

Columns B & C (I didn't specify C before cause I thought I would be
able to do that one if I could get B!) are "options" to put an x in
that box based on whether that line item, for that store, for that day
was either B or C.

So I want to look at how many times during a given month (each
worksheet) "store 1" fell with an x in column B, and then eventually I
was going to sum all months together and have only one entry that would
continue to grow each time I updated a month...blah blah blah

When I do the sumproduct that you gave, it treats my "x's" as zero and
so the multiplication part of the function will always make the answer
be zero? right? I think?!!!!

So what do you think?

Thanks
Shalon
 
My gfriend is way smarter than me!

Anyways, she had me concatenate the two columns, then just count based
on 1x, 2x, etc...

A little more work cause I have to create that column for each
workseet, but no biggy, there are only 12 months anyways!

Yeah! I'm so excited that I got it to work.

So thanks to those of you who attempted to help me!

Shalon :)
 
Hi!

If what you did is working that's great, however, it
probably wasn't necessary.

Based on your description col A has number values from 1-
47 and col B might contain an "X". Consider this data set
that might resemble your own:

1 X
5 X
22 X
47 X
47 X
21 X
30 X
47 X
5 X
21 X
47 X

You wanted to know how many times col A = a certain store
and had a corresponding "X" in col B. The formula I posted
should work. Say you want the count of store 47:

=SUMPRODUCT(--(A5:A15=47),--(B5:B15="X"))

The answer is 4.

Notice that in the formula both references to the
different columns are the exact same size and shape. This
is necessary when using the sumproduct function.

Hope this clarifies things a little.

Biff
 
I must have typed it in wrong the first time, cause now it totall
works... hmmmm...

Ok, so since you are the expert, maybe you can help me with anothe
little prob I have...

So, now I go in and do the sumproduct function that gives me that stor
info for that month. But what I really need is a total for each stor
for the year.

Being the simpleton that I am, I would simply go in, type th
sumproduct for each month and then add another behind it, etc. So i
would end up

=SUMPRODUCT(--(JanStore=3),--(JanOffice="x"))+SUMPRODUCT(--(FebStore=3),--(FebOffice="x"))+etc...

no biggy, except that I have to do that for each of the 47 stores, fo
more than one column of x's...

How do I "autofill" something complicated like this??? Can I someho
put the ranges in so that one sentence calculates all the months fo
one store?

And then if not, if I have to have the entire function+function stuff
how do I autofill and just change the store # to look at...Instead o
just doing one store, pasting onto all other 47 and then manually goin
in and changing the #s in the functions...total nightmare and it give
me a headache...

Biff- your the best!
Thanks
Shalo
 
Hi!

This is where good planning can really payoff and make
things simple! If you setup the sheets for each month
exactly the same, you could use a formula like this to get
the yearly total for the stores:

=SUM(SHEET2:SHEET47!A1)

Another tip: Insted of using the variables, store #
and "X", in the formula:

=SUMPRODUCT(--(A5:A250=47),--(B5:B250="X")

put the variables in cells and use those cells as the
reference in the formula. Say you put in A1 the store # 47
and in B1 you put the other variable, X. Now the formula
can be written like this:

=SUMPRODUCT(--(A5:A250=A1),--(B5:B250=B1)

This gives you much more versatility as you can use one
formula to calculate any number of variables.

If you can give me more info as to *exactly* how your
workbook is setup and *exactly* what things you need help
with, I'll be glad to help out!

Biff
 
Biff- you're my hero! My new favorite excel helper!

This workbook is just tracking the days that our stores are closing ou
over/short.

Ok, so each of my sheets are set up exactly the same, and each sheet i
called a month.

Column A-date, B-store#, C-amt, D-if I fixed it (C&D theoreticall
aren't going to be used for any of my function things.)

Column E- gets an x if the overage was caused by a comp/office problem
and column F gets an x if it was the stores error.

Columns G,H,I get a yes or no based on whether they gave an explanatio
(G) and signed two spots on their closing sheet (H&I.)

Columns J&K get x's based on whether or not an adjustment to GL (J) o
AR (K) has to be completed.

So my purpose for all of this (really, I do have one!) is to creat
some charts and totals per store to send to the District Managers & th
controller, that they can easily identify where and what the issue
are.

So I have one bar chart the shows only the number of times each stor
appears in my report, based on entries in column B for each workbook
(easy, I managed to make that one myself. ) and the DM's can easil
spot the sucky stores.

So now I was trying to come up with totals that would show, out of th
number of times the store is in this workbook-

1) the number of times they messed up, or it was a tech error (I hav
these totals already cause I had done the concatenate stuff before
got your 2nd response, and had made myself dizzy replacing values!)

2) how many times they provided notes

3) how many times they actually signed the two spots.

4) how many adjustments we had to make to fix their stupid errors.

I guess it might actually be good to have my answers be
percentage...but I'm not sure if I want that, cause I might make som
pretty little charts and such. I can always calculate that later in
diff column...

So hopefully that is enough info, and I absolutely appreciate all o
the help you have given me.

Shalo
 
ok, so I have been trying to do some stuff on my own...but not gettin
much out of it.

I was thinking that based on how you had me set up th
=SUMPRODUCT(--(A5:A250=A1),--(B5:B250=B1) that I could modify it
little to get what I needed for the entire book...

So what I did wa
=sumproduct(--(Jan:Dec!B2:B250=A2),--(Jan:Dec!E2:E250="x")) but i
doesn't like that and tells me #name?

I thought it looked good based on the fact that if I leave out th
worksheet names as a range, Jan:Dec, and just do the one worksheet fo
the month of January a
=SUMPRODUCT(--(Jan!B2:B250=A2),--(Jan!E2:E250="x")) it totall
works... so what did I do wrong?

Thanks
Shalo
 
Hi!

=sumproduct(--(Jan:Dec!B2:B250=A2),--(Jan:Dec!
E2:E250="x"))

In this formula you have multiple array references when
you can have only one:

Jan:Dec is one array and B2:B250 is the second. As you
discovered, it works with just one array reference.

The best way to do this is to calculate all the individual
stuff on each of the monthly sheets. Make sure that all
the formulas are in the same cell on each sheet. Then on
the summary sheet you can use something like this:

=SUM(JAN:DEC!A1)

If you want to and are able to, you can send me the
workbook to have a look. Just be *very* explicit with
details of *exactly* what you want. I can be contacted at:

VALKO01 who loves this stuff is AT a place called COMCAST
DOT NET.

Biff
 
pretty much... I guess... but my individual months don't do any of thi
summing/counting, and I only really want like a YTD summary tha
changes whenever I update within any of the monthly worksheets..
 
ummmm.... I'm confused... Not feeling cool at this point, cause I hav
no idea what to do with the information you wrote as your contac
info...

"VALKO01 who loves this stuff is AT a place called COMCAST
DOT NET."

what?!?!?!? Am I totally missing something? cause I was expecting t
see an email adrs or something, but...

I would love to have you look at my workbook and give me som
suggestions, but you are going to have to give me a little better ide
of how and where to send that to!

Totally feeling computer illiterate!
Shalo
 
Back
Top