Need a formula for Excel 2000..

S

Saltrams

Greetings & bless your little cottons for existing. I've never don
anything more than autosum before and now my workplace has gifted m
with a spreadsheet project that looks monumental to me. Can anyone tel
me how.....
If, in Sheet 1, the cell range C2-C41 contains "Cohen" anywhere AND th
cell range R2-R41 contains "allowed" anywhere, I need to have th
number of times that these two conditions are satisfied displayed a
that number of times, in Sheet 2 at cell B2.
So far I have =(IF((C2:C41="Cohen")*(R2:R41="Allowed"),)
I can't find the commands that equate in my mind to "use this value" o
"insert this value here" in the help files.
Looks like I will be spending a lot of time with you in the future!
:confused: Saltram
 
F

Frank Kabel

Hi
enter the following formula
=SUMPRODUCT(--('sheet1'!C2:C41="Cohen"),--('sheet1'!R2:R41="Allowed"))

or
=SUMPRODUCT(('sheet1'!C2:C41="Cohen")*('sheet1'!R2:R41="Allowed"))
 
A

Andy Brown

I've never done
anything more than autosum before and now my workplace has gifted me
with a spreadsheet project that looks monumental to me.
Click to expand...

Yeah, they'll do that. Sometimes it's best to pretend that you don't know
the first thing about anything.

You don't say if these are "whole cell" strings ; try

=COUNTIF(Sheet1!C2:C41,"Cohen")*COUNTIF(Sheet1!R2:R41,"allowed")

HTH,
Andy
 
F

Frank Kabel

Hi Andy
I think this won't work :)
e.g. consider the following example data
A B
1 Cohen not allowed
2 Willow allowed
3 Willow not allowed
4 Cohen allowed

I would expect a result of 1 (only the last row fullfills both
criteria). Your formula would return
=COUNTIF(A1:A4,"Cohen")*COUNTIF(B1:B4,"allowed")
=2 * 2
=4
 
A

Andy Brown

Hi Andy
I think this won't work :)
Click to expand...

Thanks Frank. It won't work for (count C = "Cohen" where R *in the same row*
= "allowed"). If that's what required, then the wording of the question is
flawed.

Rgds,
Andy
 
F

Frank Kabel

Hi andy
after reading the original post again you're right. The question is not
that precise :)
But in most cases this kind of question is typical an AND condition for
the same row. But we 'll only know if the OP responds again

wish you a nice weekend
 
A

Andy Brown

But in most cases this kind of question is typical an AND condition for
the same row.
Click to expand...

You're probably right ; I expect all those "anywhere"s led me astray.

Have a good one yourself, rgds,
Andy
 
S

Saltrams

Sorry my question wasn't precise. That will be 'cos I don't know wha
I'm doing!!! I'll try again:
Sheet 1 ranges A2 to S41. There will be 40 rows of information onc
entered. "Cohen" is one of 5 names that will appear on any row i
column J. In column R there can only be "allowed" or "Dismissed" (
have restricted this using a drop down list by using data validation).
Each row will contain a name (Cohen, for example) and EITHER "allowed
or "Dismissed".
I need to record the statistics of how many "allowed" each name (lik
Cohen) gets and the same for how many "dismissed" each person gets.
then want those statistics to appear in another spreadsheet that I hav
designed in sheet 2. This one shows how many "allowed" and how man
"dismissed" each of the names gets every month.
I hope that makes it clearer. But I will be trying the formulae yo
guys supplied in the meantime - part of the learning process.
Thanks for all your help.
;
 
P

pgeraf

I think this is what you were looking for. Hope you like it.

Sheet1

Col A / Col B / Col C
Name / Condition / (Concatenated cells)
Stewart / Allowed / Stewart Allowed (Format cells in Col
as ;;; to hide their contents)
Cohen / Allowed / Cohen Allowed (Formula in Col C: =A2&
"&B2)
Jones / Dismissed / Jones Dismissed


Sheet2
Col A / Col B / Col C
Name / Condition / Times condition is fulfilled
Cohen / Allowed / Formula in Col C
=COUNTIF(Sheet1!C2:C15;A2&" "&B2
 
P

pgeraf

Ok, Saltrams, what you need is an Array formula (though my previou
suggestion should work fine, this one is more straight forward).

All you've got to do is this: in Sheet 2 at cell A1 write the name yo
want to check. In cell B1 type the condition to be fulfilled. In cel
C1 type the following formula:

=SUM(IF((SHEET1!C2:C41=$A$1)*(SHEET1!R2:R41=$B$1),1,0))

Now (and this is extremely important) DO NOT HIT "ENTER", but "CTRL"
"SHIFT" + "ENTER". After doing so your formula should look like this:

{=SUM(IF((SHEET1!C2:C41=$A$1)*(SHEET1!R2:R41=$B$1),1,0))}

Now, whenever you enter a name and a condition in cells A1 and B1 i
Sheet 2, the formula will search and count how many times the entere
parameters are matched in Sheet 1.

Enjoy! :
 
S

Saltrams

Thanks for all that. I won't pretend to understand, but I can follo
instructions as well as the next person. ;
 
A

AlfD

Hi!

Something in this rings a bell with me, but it's over 40 years since
last heard it. So bear with me if I'm off-message.

What you are doing sound very much like matrix multiplication (vecto
product? cross product?) except that my memories would have the value
matrix first: multiply by the eigenvectors matrix and produce th
results matrix which would have the dimensions of Values bu
transposed.

If so, Excel has a function MMULT which multiplies matrices an
internal functions are generally quicker than "made up" ones.


Al
 
J

Jerry W. Lewis

The OP's matrix exceeds the maximum size of the MMULT output matrix
(which is >=5460 cells and <5467 cells)

Jerry
 
Top