count if with multiple criteria

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Bertha needs help - 29 Jul 2008 19:08 GMT

I a worksheet i have a list of different alarms on column D. Then on column C
it states if its an "In" or "Out" alarm. So, I want to count in a different
sheet how many times each certain alarm comes up when its "In".

Reply to this Message

T. Valko - 29 Jul 2008 19:30 GMT

Try something like this:

=SUMPRODUCT(--(Sheet1!C2:C100="in"),--(Sheet1!D2:D100="alarm1"))

Replace "alarm1" with the actual name of the alarm, or, better yet, use a
cells to hold the criteria:

A2 = in
B2 = alarm1

=SUMPRODUCT(--(Sheet1!C2:C100=A2),--(Sheet1!D2:D100=B2))

Signature

Biff
Microsoft Excel MVP

>I a worksheet i have a list of different alarms on column D. Then on column
>C
> it states if its an "In" or "Out" alarm. So, I want to count in a
> different
> sheet how many times each certain alarm comes up when its "In".

Reply to this Message

Bertha needs help - 30 Jul 2008 19:43 GMT

ok is there something wrong with my formula?
=SUMPRODUCT(--(Sheet3!C:C="InAlm"),--(Sheet3!D:D='BF4 Alarms'!A8))

It returns a NUM# error

> I a worksheet i have a list of different alarms on column D. Then on column C
> it states if its an "In" or "Out" alarm. So, I want to count in a different
> sheet how many times each certain alarm comes up when its "In".

Reply to this Message

RagDyer - 30 Jul 2008 20:39 GMT

You're using *entire* column references (C:C - D:D) which will only work in
XL07!

Are you using that version?

Signature

Regards,

RD

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

>
> ok is there something wrong with my formula?
[quoted text clipped - 7 lines]
>> different
>> sheet how many times each certain alarm comes up when its "In".

Reply to this Message

Bertha needs help - 31 Jul 2008 12:45 GMT

No i am using windows 2003

Ok i changed it YES!! it works

Thank you so much ;D

this is the new formula

=SUMPRODUCT(--(Sheet3!C1:C2800="InAlm"),--(Sheet3!D1:D2800='BF4 Alarms'!A8))

> ok is there something wrong with my formula?
> =SUMPRODUCT(--(Sheet3!C:C="InAlm"),--(Sheet3!D:D='BF4 Alarms'!A8))
[quoted text clipped - 4 lines]
> > it states if its an "In" or "Out" alarm. So, I want to count in a different
> > sheet how many times each certain alarm comes up when its "In".

Reply to this Message

RagDyeR - 31 Jul 2008 17:18 GMT

I'm sure Biff appreciates your appreciation of his formula!<g>

Signature

Regards,

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

No i am using windows 2003

Ok i changed it YES!! it works

Thank you so much ;D

this is the new formula

=SUMPRODUCT(--(Sheet3!C1:C2800="InAlm"),--(Sheet3!D1:D2800='BF4 Alarms'!A8))

> ok is there something wrong with my formula?
> =SUMPRODUCT(--(Sheet3!C:C="InAlm"),--(Sheet3!D:D='BF4 Alarms'!A8))
[quoted text clipped - 6 lines]
> > different
> > sheet how many times each certain alarm comes up when its "In".

Reply to this Message