If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
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. |
#2
|
|||
|
|||
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:260)) 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. |
#3
|
|||
|
|||
Multiple criteria - count if or sumproduct?
Try these formulas =SUMPROUCT((A1:E1="East")*(A2:E2=40)) and =SUMPROUCT((A1:E1="East")*(A2:E260)*(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 |
#4
|
|||
|
|||
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:260)) 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. |
Thread Tools | |
Display Modes | |
|
|