Multiple criteria - count if or sumproduct?
 

Hi all, hoping someone out there can help with a formula question.

I have a set of data similar to the following

A B C
D E

1 East West North East
North
2 45 40 51 35
20


Row 1 refers to the locality, and row two to the opening hours a week.

I want to be able to ask, how many places are in the east and are open less
than or equal to 40 hours a week (2 criteria), and then build on this by
asking how many places in the east are are open more than 40 hours a week and
less than 60 hours a week (3 criteria).
I have tried several versions of countif and sumproduct but all to no avail.
Would be far simpler to use countifs (as in 2007) but my employers only use
2003!
Any help very much appreciated.

Multiple criteria - count if or sumproduct?
 

---how many places are in the east and are open less
than or equal to 40 hours a week (2 criteria),

=SUMPRODUCT(--(1:1="East"),--(2:2<=40),--(2:20))

---how many places in the east are are open more than 40 hours a week and
less than 60 hours a week (3 criteria).

=SUMPRODUCT(--(1:1="East"),--(2:240),--(2:2<60))

If this post helps click Yes
---------------
Jacob Skaria


"dizzyflossy" wrote:

Hi all, hoping someone out there can help with a formula question.

I have a set of data similar to the following

A B C
D E

1 East West North East
North
2 45 40 51 35
20


Row 1 refers to the locality, and row two to the opening hours a week.

I want to be able to ask, how many places are in the east and are open less
than or equal to 40 hours a week (2 criteria), and then build on this by
asking how many places in the east are are open more than 40 hours a week and
less than 60 hours a week (3 criteria).
I have tried several versions of countif and sumproduct but all to no avail.
Would be far simpler to use countifs (as in 2007) but my employers only use
2003!
Any help very much appreciated.

Multiple criteria - count if or sumproduct?
 

Try these formulas

=SUMPROUCT((A1:E1="East")*(A2:E2<=40))

and

=SUMPROUCT((A1:E1="East")*(A2:E2<60)*(A2:E240))


--
barry houdini
------------------------------------------------------------------------
barry houdini's Profile: http://www.thecodecage.com/forumz/member.php?userid=72
View this thread: http://www.thecodecage.com/forumz/sh...d.php?t=127811

Multiple criteria - count if or sumproduct?
 

Hi there, thanks for your almost instant response! The 3 criteria one worked
perfectly. The 2 criteria one returned "0", until I took off the third part
of the formula, the bit that says "(2:20))", now returning expected result!

Fabulous, thanks so much for your help!!!

"Jacob Skaria" wrote:

---how many places are in the east and are open less
than or equal to 40 hours a week (2 criteria),

=SUMPRODUCT(--(1:1="East"),--(2:2<=40),--(2:20))

---how many places in the east are are open more than 40 hours a week and
less than 60 hours a week (3 criteria).

=SUMPRODUCT(--(1:1="East"),--(2:240),--(2:2<60))

If this post helps click Yes
---------------
Jacob Skaria


"dizzyflossy" wrote:

Hi all, hoping someone out there can help with a formula question.

I have a set of data similar to the following

A B C
D E

1 East West North East
North
2 45 40 51 35
20


Row 1 refers to the locality, and row two to the opening hours a week.

I want to be able to ask, how many places are in the east and are open less
than or equal to 40 hours a week (2 criteria), and then build on this by
asking how many places in the east are are open more than 40 hours a week and
less than 60 hours a week (3 criteria).
I have tried several versions of countif and sumproduct but all to no avail.
Would be far simpler to use countifs (as in 2007) but my employers only use
2003!
Any help very much appreciated.