countif formula

J

jh

Could I have help with this formula:

In SHEET1, if G2="Hire" and A2="08-2004" and D2="Manager",
then count how many records there are and put that in
SHEET2 Cell H2.

Note: A2 on SHEET1 is formatted as MM-YYYY (not sure if
this makes a difference).

Thanks in advance.
 
R

RagDyeR

Where are the records to count?
What kind of records ?

Don't forget, you know what you're talking about, BUT ... no one else in
this group does.
Without you giving accurate, informative descriptions of your set-up, it's
difficult or impossible for anyone to offer viable solutions or even
suggestions.
--

Regards,

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

Could I have help with this formula:

In SHEET1, if G2="Hire" and A2="08-2004" and D2="Manager",
then count how many records there are and put that in
SHEET2 Cell H2.

Note: A2 on SHEET1 is formatted as MM-YYYY (not sure if
this makes a difference).

Thanks in advance.
 
B

Bob Phillips

=SUMPRODUCT(--(Sheet1!A3:A100=Sheet1!A2),--(Sheet1!G3:G100=Sheet1!G2),--(She
et1!D3:D100=Sheet1!D2)

I am sure you can adjust the ranges to suit.

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)
 
J

jh

I posted this on another website and here's what I got.
I'm posting it here in case it would be helpful to anyone
else. Thanks a bunch.

***
For multiple constraints like this, the best approach has
been found to be a SUMPRODUCT formula like:
=SUMPRODUCT((Sheet1!G2:G65536="Hire")*(MONTH(Sheet1!
A2:A65536)=8)*(YEAR(Sheet1!A2:A65536)=2004)*(Sheet1!
D2:D65536="Manager"))

In SUMPRODUCT formulas like these, each constraint must be
enclosed in parentheses, and the range may not extend to
an entire column. It is acceptable for the range to extend
past the data, however, as shown in the suggested formula.

Note that Microsoft does not document the use of
SUMPRODUCT in this fashion, but the trick is widely known
in places like Tek-Tips. It works by converting each
constraint expression into a Boolean array of True and
False values. These in turn are interpreted as 1 or 0--so
it is the same as requiring that each constraint in the
row be True before that row contributes to the total count.
***
 
B

Bob Phillips

Which is exactly what I offered you in my response.

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)
 
Top