software

2 columns (F amp; G) with several rows. Column “F” includes 7 code letters (C,E,
J, and etc.) for coding. Column G has only 2 codes. Either “M” or “F”. I want
to show in a total box all the “J” “M”’s in one cell (H36) and show all
total of “J”; “F”’s (H37) in another separate total cell discounting all
other codes C,E and etc from Column “F”.

I hope this makes sense. Thank you.Enter this in H37 and change the letter F for M in H36. I did this with the
conditional sum wizard. Therefore you need a title above each column.
=SUM(IF($F$4:$F$35=quot;Jquot;,IF($G$4:$G$35=quot;Fquot;,1,0),0)) entered as an array
formula should work.
Use the following keys to create the array formula Ctrl Shft Enter

HTH

Gillesquot;Davequot; gt; wrote in message
...
gt;2 columns (F amp; G) with several rows. Column quot;Fquot; includes 7 code letters
gt;(C,E,
gt; J, and etc.) for coding. Column G has only 2 codes. Either quot;Mquot; or quot;Fquot;. I
gt; want
gt; to show in a total box all the quot;Jquot; quot;Mquot;'s in one cell (H36) and show all
gt; total of quot;Jquot;; quot;Fquot;'s (H37) in another separate total cell discounting all
gt; other codes C,E and etc from Column quot;Fquot;.
gt;
gt; I hope this makes sense. Thank you.
gt;
In I36, put M, I37 put F.

In H36, =SUMPRODUCT(--($G$2:$G$200=quot;Jquot;),--($H$2:$H$200=I36))

and copy to H37

--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)

quot;Davequot; gt; wrote in message
...
gt; 2 columns (F amp; G) with several rows. Column quot;Fquot; includes 7 code letters
(C,E,
gt; J, and etc.) for coding. Column G has only 2 codes. Either quot;Mquot; or quot;Fquot;. I
want
gt; to show in a total box all the quot;Jquot; quot;Mquot;'s in one cell (H36) and show all
gt; total of quot;Jquot;; quot;Fquot;'s (H37) in another separate total cell discounting all
gt; other codes C,E and etc from Column quot;Fquot;.
gt;
gt; I hope this makes sense. Thank you.
gt;
I tried but must be doing something wrong. It would not work. What I would
like to do is to forget the actual working copy and create a very simple
similiar worksheet using just 2 columns and 8 rows. Try a formula for that
and if I can get that to work along with quot;myquot; understanding of how it is to
work, then I can go back to the real worksheet and go from there. Make sense?
I apoligize for my ignornance and would appreciate some assistance. I'm using
a worksheet created by another. The way the cells are arranged on it might be
the causing the error.

Thank you in case you are willing to assist.

Dave

quot;Gilles Desjardinsquot; wrote:

gt; Enter this in H37 and change the letter F for M in H36. I did this with the
gt; conditional sum wizard. Therefore you need a title above each column.
gt; =SUM(IF($F$4:$F$35=quot;Jquot;,IF($G$4:$G$35=quot;Fquot;,1,0),0)) entered as an array
gt; formula should work.
gt; Use the following keys to create the array formula Ctrl Shft Enter
gt;
gt; HTH
gt;
gt; Gilles
gt;
gt;
gt; quot;Davequot; gt; wrote in message
gt; ...
gt; gt;2 columns (F amp; G) with several rows. Column quot;Fquot; includes 7 code letters
gt; gt;(C,E,
gt; gt; J, and etc.) for coding. Column G has only 2 codes. Either quot;Mquot; or quot;Fquot;. I
gt; gt; want
gt; gt; to show in a total box all the quot;Jquot; quot;Mquot;'s in one cell (H36) and show all
gt; gt; total of quot;Jquot;; quot;Fquot;'s (H37) in another separate total cell discounting all
gt; gt; other codes C,E and etc from Column quot;Fquot;.
gt; gt;
gt; gt; I hope this makes sense. Thank you.
gt; gt;
gt;
gt;
gt;

I apoligize for my ignorance. Must be old age. Anyway the formula works and I
thank you for your time.

Dave

quot;Bob Phillipsquot; wrote:

gt; In I36, put M, I37 put F.
gt;
gt; In H36, =SUMPRODUCT(--($G$2:$G$200=quot;Jquot;),--($H$2:$H$200=I36))
gt;
gt; and copy to H37
gt;
gt; --
gt; HTH
gt;
gt; Bob Phillips
gt;
gt; (remove nothere from email address if mailing direct)
gt;
gt; quot;Davequot; gt; wrote in message
gt; ...
gt; gt; 2 columns (F amp; G) with several rows. Column quot;Fquot; includes 7 code letters
gt; (C,E,
gt; gt; J, and etc.) for coding. Column G has only 2 codes. Either quot;Mquot; or quot;Fquot;. I
gt; want
gt; gt; to show in a total box all the quot;Jquot; quot;Mquot;'s in one cell (H36) and show all
gt; gt; total of quot;Jquot;; quot;Fquot;'s (H37) in another separate total cell discounting all
gt; gt; other codes C,E and etc from Column quot;Fquot;.
gt; gt;
gt; gt; I hope this makes sense. Thank you.
gt; gt;
gt;
gt;
gt;