Hi,
I have type of centres (Primary, secondary, teritiary) in one column and
another column with 1 or 0. I want a command that says Multiply centres which
has '1' in the other column. How do i do it?


"T. Valko" wrote:

Assuming you have a column of true Excel dates...

=SUMPRODUCT(--(A1:A100="Primary"),--(TEXT(B1:B100,"myyyy")="42008"))

Or:

=SUMPRODUCT(--(A1:A100="Primary"),--(MONTH(B1:B100)=4),--(YEAR(B1:B100)=2008))

--
Biff
Microsoft Excel MVP


"Krishnan" wrote in message
...
Hi,
I tried what you said and it doesnt seem to work. In one column i have
type
of centre (whether centre is Tertiary, secondary or Primary) and in
another
column i have operation start date (Month when operation started). If i
want
to know how many primary centres were started in April 2008 how do i go
about
doing it?

When i use SUMPRODUCT function like how you have given below, i get the
count of Primary centres but i do not get the count of primary centres
started in April 2008.

Regards
Krishnan

"Luke M" wrote:

Guessing xlfn = Excel Function.

It sounds like although Excel was nice enough to leave the formula in, it
wasn't actually calculating. It was just able to remember what value it
had
calculated when you had it open in 2007.

As there is no COUNTIFS in 2003, as soon as you asked it to recalculate
(by
adding a number) XL gave up on trying to figure it out. This is similar
to
what happenes whne you use a function from an Add-in, but don't have the
add-in activated.

A recommendation would be to replace your COUNTIFS with SUMPRODUCT.
Example:
=SUMPRODUCT(((A1:A100="Tom")+(A1:A100="Bob"))*1)
would give you a count of all the Tom and Bob's in a1:a100
--
Best Regards,

Luke M
*Remember to click "yes" if this post helped you!*


"Steve" wrote:

I created some countifs in 2007, saved it down to 2003. When opened in
2003,
in front of the countifs is: =_xlfn.COUNTIFS

It showed correctly in 2003, however, when I added another number in
the
range that the countifs were counting, an #name? error was produced,
and it
couldn't be undone. Is there any way around this ? Though I'm still
impressed
that 2003 was able to keep the 2007 results to begin with. What does
xlfn
mean ?

Thanks,


Steve



.