Hi Penny
If that is the case, and that there will be values in column B of Sheet1
when column A is blanks, then you will need to revert to the Sumproduct
formula given to you by Exl-Learner, but modified to work on Sheet2.
Enter in Sheet2 cell B2 the following
=IF($A2="","",SUMPRODUCT((Sheet1!$B$2:$B$1000=$A2) *(Sheet1!$A$2:$A$1000<"")))
and copy down as required
--
Regards
Roger Govier
"Penny" wrote in message
...
Okay how about a whole different slant... If the Column A has a "0" in it
it
should not be included in the total count that this formula is turning
around. Row 6 in the example below is blank... say it has a 0 in it but
I
don't want that included in the count... Help please?????
Thanks.
"Roger Govier" wrote:
Hi Penny
If you want a zero to appear, change the formula to
=IF(A2="",0,COUNTIF(Sheet1!B:B,A2))
If you say that there are no blanks or zero's appearing then the cells
that
you think are blank, are probably not. They may contain a space
character,
which will not be visible.
Try pressing delete on those cells in column A that you think should be
blank.
--
Regards
Roger Govier
"Penny" wrote in message
...
Great. This works perfect with one exception. When I have a blank
field
and
it is not giving me 0 or blank.
"Roger Govier" wrote:
Hi Penny
Set up a new table (say on sheet 2) with just a list of week numbers
in
column A starting at A2
Then in B2 enter
=IF(A2="","",COUNTIF(Sheet1!B:B,A2))
Copy down column B on Sheet2 as far as you wish.
--
Regards
Roger Govier
"Penny" wrote in message
...
Very cool. Now I have two additional variables to add to the
equation.
First I only want 1 total number per week in column C. In this
formula
I
am
getting C1 and C2 with the Total count of 2 in it. I only want it
to
be
in
C1. Second is there a way I can paste new data into my columns and
not
have
to redo the formula. Example would be next week when I past data I
might
have 4 types in column 1 for wk 77 instead of 2.
"Ms-Exl-Learner" wrote:
Assume that you are having the Values in Column A & B Like the
below:-
Col A Col B
Row1 a 77
Row2 b 77
Row3 c 78
Row4 d 80
Row5 e 80
Row6 81
In C1 cell paste the below formula
=SUMPRODUCT(($B$1:$B$100=$B1)*($A$1:$A$100<""))
Copy the C1 cell and paste it to the remaining cells of C Column.
Remember to Click Yes, if this post helps!
--------------------
(Ms-Exl-Learner)
--------------------
"Penny" wrote:
Need a formula.
Column 1 (type) Column 2 (wk)
a 77
b 77
c 78
d 80
e 80
blank 81
I would like the formula to come back for every week and tell me
the
number
of types that are in that week. Another words in this case there
are
qty 2
types in wk 77. In week 81 there are 0. In wk 78 there is 1 and
week
80
there is 2.
Can anybody help?
Thanks.
__________ Information from ESET Smart Security, version of virus
signature database 4836 (20100204) __________
The message was checked by ESET Smart Security.
http://www.eset.com
__________ Information from ESET Smart Security, version of virus
signature database 4836 (20100204) __________
The message was checked by ESET Smart Security.
http://www.eset.com
__________ Information from ESET Smart Security, version of virus
signature database 4839 (20100205) __________
The message was checked by ESET Smart Security.
http://www.eset.com
__________ Information from ESET Smart Security, version of virus
signature database 4839 (20100205) __________
The message was checked by ESET Smart Security.
http://www.eset.com
__________ Information from ESET Smart Security, version of virus
signature database 4849 (20100208) __________
The message was checked by ESET Smart Security.
http://www.eset.com
__________ Information from ESET Smart Security, version of virus signature database 4849 (20100208) __________
The message was checked by ESET Smart Security.
http://www.eset.com