Need help with an IF formula

[CrewDog130]

Can I tell a cell to look at the entire sheet for the chosen text and i
found look for a chosen condition and count the amount of times thi
condition occurs?

I'm pretty sure some kind of of "IF" function will solve this but
can't figure it out.

I've attached an example of the spread that I'm building. Thanks fo
any help you can give m

+-------------------------------------------------------------------
|Filename: Help.xlsx
|Download: http://www.thecodecage.com/forumz/attachment.php?attachmentid=202
+-------------------------------------------------------------------

 

[Simon Lloyd]

Can I tell a cell to look at the entire sheet for the chosen text and i
found look for a chosen condition and count the amount of times thi
condition occurs

I'm pretty sure some kind of of "IF" function will solve this but
can't figure it out.

I've attached an example of the spread that I'm building. Thanks fo
any help you can give meI think you mean Sumproduct used like this

*=SUMPRODUCT((D4100=A1)*(K4:K100<60)) *where A1 would be your text t
check for, this formula says look for all the cells that match A1 the
if their corresponding cell in column K is less than 60 COUNT it (yo
stated count rather than sum), one thing to note with sumproduct is tha
the ranges must be identical dimensions so if you have A1:A100 and ar
counting in column B then you must use B1:B100

--
Simon Lloy

Regards
Simon Lloy
'Microsoft Office Help' (http://www.thecodecage.com

 

[Rick Rothstein]

Your attachment did not come through. A couple of questions... When you said
"entire sheet" did you really mean that... every cell, including the one the
formula is in? What are the "chosen conditions" you want to test for?

 

[CrewDog130]

I think you mean Sumproduct used like this:
*=SUMPRODUCT((D4100=A1)*(K4:K100<60)) *where A1 would be your text t
check for, this formula says look for all the cells that match A1 the
if their corresponding cell in column K is less than 60 COUNT it (yo
stated count rather than sum), one thing to note with sumproduct is tha
the ranges must be identical dimensions so if you have A1:A100 and ar
counting in column B then you must use B1:B100.

Thanks, That worked perfectly. all I had to due was put A1 i
quotations. I looked at the SUMPRODUCT formula but didn't understand it
Thanks again for your help

 

[Simon Lloyd]

Thanks, That worked perfectly. all I had to due was put A1 i
quotations. I looked at the SUMPRODUCT formula but didn't understand it
Thanks again for your help

Glad we could be of help

--
Simon Lloy

Regards
Simon Lloy
'Microsoft Office Help' (http://www.thecodecage.com