countif with some conditions

B

BenEastCoast

I have 2 WS (pls see attached), The first 1 I update regularly th
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 &#9786

+-------------------------------------------------------------------
|Filename: countif.jpg
|Download: http://www.excelbanter.com/attachment.php?attachmentid=910
+-------------------------------------------------------------------
 
L

lhkittle

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|

+-------------------------------------------------------------------+

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
 
C

Claus Busch

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.
 
C

Claus Busch

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.
 
C

Claus Busch

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.
 
B

BenEastCoast

Claus said:
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.

I will try your formula. Thanks for the reply Claus B. Cheer

+-------------------------------------------------------------------
+-------------------------------------------------------------------
 
B

BenEastCoast

BenEastCoast;1613751 said:
I will try your formula. Thanks for the reply Claus B. Cheers

****

Thanks a lot for the formula Claus B. It works

+-------------------------------------------------------------------
+-------------------------------------------------------------------
 
B

BenEastCoast

Give these a try.
-
=SUMPRODUCT(--($A$2:$A$10="TEM")*($B$2:$B10="PM"))-

Regards,
Howard

Thanks for the reply Howard, I got it working :)


+-------------------------------------------------------------------+
+-------------------------------------------------------------------+
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top