Home |
Search |
Today's Posts |
#1
|
|||
|
|||
countif with some conditions
I have 2 WS, The first 1 I update regularly the second 1 is my monthly summary.
I use countif in WeeklySummary to tally the am & pm, the formula I have is =IF(A:A="permanent", COUNTIF(B:B,"am")) "am" that is permanent "am" that is temporary "pm" that is permanent "pm" that is temporary Thanks in advance for any help ☺ Last edited by BenEastCoast : September 5th 13 at 12:15 PM |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
countif with some conditions
On Wednesday, September 4, 2013 9:02:25 PM UTC-7, BenEastCoast wrote:
I have 2 WS (pls see attached), The first 1 I update regularly the second 1 is my monthly summary. I use countif in WeeklySummary, how do I count the: "am" that is permanent "am" that is temporary "pm" that is permanent "pm" that is temporary Thanks in advance for any help ☺ +-------------------------------------------------------------------+ |Filename: countif.jpg | |Download: http://www.excelbanter.com/attachment.php?attachmentid=910| +-------------------------------------------------------------------+ -- BenEastCoast Give these a try. "am" that is permanent =SUMPRODUCT(--($A$2:$A$10="PER")*($B$2:$B10="AM")) "am" that is temporary =SUMPRODUCT(--($A$2:$A$10="TEM")*($B$2:$B10="AM")) "pm" that is permanent =SUMPRODUCT(--($A$2:$A$10="PER")*($B$2:$B10="PM")) "pm" that is temporary =SUMPRODUCT(--($A$2:$A$10="TEM")*($B$2:$B10="PM")) Regards, Howard |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
countif with some conditions
Hi Ben,
Am Thu, 5 Sep 2013 05:02:25 +0100 schrieb BenEastCoast: I have 2 WS (pls see attached), The first 1 I update regularly the second 1 is my monthly summary. I use countif in WeeklySummary, how do I count the: "am" that is permanent "am" that is temporary "pm" that is permanent "pm" that is temporary in B2 of Book2: =SUMPRODUCT(--([Book1]Sheet1!$A$2:$A$100=$A2),--([Book1]Sheet1!$B$2:$B$100=B$1)) and copy to the right and down. Regards Claus B. -- Win XP PRof SP2 / Vista Ultimate SP2 Office 2003 SP2 /2007 Ultimate SP2 |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
countif with some conditions
Hi again,
Am Thu, 5 Sep 2013 13:38:37 +0200 schrieb Claus Busch: in B2 of Book2: =SUMPRODUCT(--([Book1]Sheet1!$A$2:$A$100=$A2),--([Book1]Sheet1!$B$2:$B$100=B$1)) sorry, I had a false sheet name. Try: =SUMPRODUCT(--([Book1.xlsx]Tabelle1!$A$2:$A$100=$A2),--([Book1.xlsx]Tabelle1!$B$2:$B$100=B$1)) or in Excel version 2007 or later: =COUNTIFS([Book1.xlsx]Tabelle1!$A$2:$A$100,$A2,[Book1.xlsx]Tabelle1!$B$2:$B$100,B$1) Regards Claus B. -- Win XP PRof SP2 / Vista Ultimate SP2 Office 2003 SP2 /2007 Ultimate SP2 |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
countif with some conditions
hi,
Am Thu, 5 Sep 2013 13:50:27 +0200 schrieb Claus Busch: =SUMPRODUCT(--([Book1.xlsx]Tabelle1!$A$2:$A$100=$A2),--([Book1.xlsx]Tabelle1!$B$2:$B$100=B$1)) or in Excel version 2007 or later: =COUNTIFS([Book1.xlsx]Tabelle1!$A$2:$A$100,$A2,[Book1.xlsx]Tabelle1!$B$2:$B$100,B$1) sorry I copied the wrong formula. =COUNTIFS([Book1.xlsx]IUpdateRegularly!$A$2:$A$100,$A2,[Book1.xlsx]IUpdateRegularly!$B$2:$B$100,B$1) or =SUMPRODUCT(--([Book1.xlsx]IUpdateRegularly!$A$2:$A$100=$A2),--([Book1.xlsx]IUpdateRegularly!$B$2:$B$100=B$1)) Regards Claus B. -- Win XP PRof SP2 / Vista Ultimate SP2 Office 2003 SP2 /2007 Ultimate SP2 |
#6
|
|||
|
|||
Quote:
|
#7
|
|||
|
|||
Quote:
Thanks a lot for the formula Claus B. It works! |
#8
|
|||
|
|||
Quote:
|
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Countif with two conditions | Excel Worksheet Functions | |||
Countif Conditions - Use of conditions that vary by cell value | Excel Discussion (Misc queries) | |||
Countif with two conditions | Excel Discussion (Misc queries) | |||
Countif with 2 conditions | Excel Worksheet Functions | |||
countif two conditions | Excel Discussion (Misc queries) |