Formula/Function help!!

[Gadgetman]

I need help with a worksheet function for number of accidents that
have certain catagories assigned.

Column B of my worksheet is to be labled either "Reportable" -or- "Non-
Reportable".
Column C is to be labled either "EK" -or- "EA" (codes for different
locations)

I need to have a formula/function that calculates how many accidents
that are marked "Reportable" AND have the "EK" designations. Same with
"Reportable" AND "EA" etc. The worksheet is too large to count them by
eye so a formula would be great. If anyone can help, I'd appreciate
it.

Thanks

 

[Dave Peterson]

If you're using xl2007+, take a look at =countifs() in excel's help.

If you're using xl2003 (or even xl2007+), you can use something like:

=sumproduct(--(b1:b99="reportable"),--(c1:c99="ek"))

Adjust the ranges to match--but you can't use whole columns (except in xl2007+).

=sumproduct() likes to work with numbers. The -- stuff changes trues and falses
to 1's and 0's.

Bob Phillips explains =sumproduct() in much more detail here:
http://www.xldynamic.com/source/xld.SUMPRODUCT.html

And J.E. McGimpsey has some notes at:
http://mcgimpsey.com/excel/formulae/doubleneg.html

 

[Gadgetman]

Dave, Thanks for the help...
To follow up, we have Excel 2003

I tried the =sumproduct example you gave below however I keep getting
0 for the values...

I'm not sure if it matters, but the EK or EA value is attached to the
end of a # in column C (For example 0140-10/EK). Would that have an
efffect on my value for the function?

Also, would it matter since all cells in column B contain the word
"reportable" (ie: Non-Reportable or "Reportable")?

Not having much luck

 

[Pete_UK]

Yes, it does matter that you have other characters than just EK or EA
in column C. You can modify Dave's formula like this:

=sumproduct(--(b1:b99="reportable"),--(ISNUMBER(SEARCH("ek",c1:c99))))

and like this:

=sumproduct(--(b1:b99="non-reportable"),--
(ISNUMBER(SEARCH("ek",c1:c99))))

and so on, to get the other combinations. The bit I've added gives you
the equivalent of "contains".

Better still would be to put Reportable in F2, Non-reportable in F3,
EK in G1 and EA in H1, and then this formula in G2:

=sumproduct(--($b$1:$b$99=$F2),--(ISNUMBER(SEARCH(G$1,$c$1:$c$99))))

then you can copy this into H2 and copy G2:H2 into G3:H3 to get a
little table of results.

Hope this helps.

Pete

 

[Gadgetman]

Pete...
Thank you very much! Your suggestions worked. Gave us what we need.
Thank goodness for people like yourself

 

[Pete_UK]

You're welcome, Gadgetman - thanks for feeding back.

Pete