Help, Think I need to use countif or sumproduct

[JaiD]

Please try and help me with this one:

I have a spreadsheet with the following data columns:
A B C D E F
No Ac Name Staff Name Area Grade


I want a formula to look in the area column and the grade column an
tell me how many grade 4 there are working in wales etc. I can easil
type in the formula the areas i need each time thats fine just need t
know how i can get it to count only the grade 4s in wales etc

 

[Frank Kabel]

Hi
use
=SUMPRODUCT(--(E1:E100="Wales"),--(F1:F100=4))

Also see:
http://www.xldynamic.com/source/xld.SUMPRODUCT.html

 

[Guest]

JaiD,

Assuming the area is range E2:E10 and the grade is range F2:F10, try:

=SUMPRODUCT((F2:F10=4)*(E2:E10="wales")*1)

 

[RagDyer]

Instead of changing the formula itself, every time you're going to change
the lookup values, you can select two cells, and enter the "Area" and
"Grade" in them to return the count that you're looking for.

For example, enter the Area to find in G1, and the Grade to count in H1, and
try this:
=SUMPRODUCT((E1:E100=G1)*(F1:F100=H1))
--

HTH,

RD
==============================================
Please keep all correspondence within the Group, so all may benefit!
==============================================



Please try and help me with this one:

I have a spreadsheet with the following data columns:
A B C D E F
No Ac Name Staff Name Area Grade


I want a formula to look in the area column and the grade column and
tell me how many grade 4 there are working in wales etc. I can easily
type in the formula the areas i need each time thats fine just need to
know how i can get it to count only the grade 4s in wales etc.

 

[Alex Delamain]

Why not take it one step further and use a drop down to select Area an
grade using Data, Validation, Allow List?
Alternatively a pivot table would enable you to have a table showin
areas vs grades so all the info is visible in one place