# Thread: If column B has XX how many times does ZZ appear for Nov?

1. ## If column B has XX how many times does ZZ appear for Nov?

Hello I am wanting to count the number of times a word appears in a row ONLY if B column contains a particular word eg:

Names in a column A
suburb in column b
Column C - JU has Id codes under dates (daily eg: 1/11/14, 2/11/14 etc)

In a new tab I want to see results :
if column b contains the text "testing" then look across that entire row from column for only september dates ) and if the code AL appears calculate this as 1.

Does that make sence? I have attached a sample as I dont think im explaining myself properly, sorry..

isabella

2. =IF(B2="Team 3",COUNTIF(C2:JU2,"=AL"),0)

Not sure what you are wanting about the Nov part (title)
Countifs including first line = Nov (and a bit of formatting) should get you that too

3. I am thinking he might want an overall summary

=SUMPRODUCT((\$B\$2:\$B\$14="Team 3")*(\$C\$2:\$JU\$14="AL"))

4. ## count by month

Hello again,

That works to count everything:
=SUMPRODUCT(('HR Operations Leave Register'!B2:B14="Team 3")*('HR Operations Leave Register'!C2:JU14="AL"))

What I am seeking is to have it count by month as per the spreadsheet sample eg:
in Jan how many from Team 1 contain "AL"
in Feb how many from Team 2 contaiin AL
in Mar how many from team 1 contain AL
etc

Isabella

Originally Posted by WizzardOfOz
=IF(B2="Team 3",COUNTIF(C2:JU2,"=AL"),0)

Not sure what you are wanting about the Nov part (title)
Countifs including first line = Nov (and a bit of formatting) should get you that too

5. by november I mean in hte sample attachment I want to break it down by month

6. Try this

=SUMPRODUCT((Text('HR Operations Leave Register'!\$C\$1:\$JU\$1,"mmm")="Jan"))*('HR Operations Leave Register'!B2:B14="Team 3")*('HR Operations Leave Register'!C2:JU14="AL"))

and so on

7. Perhaps something like this, using array formulas (means when ENTER you need to press CTRL-SHIFT-ENTER button together)

8. ## Omg

I CANT THANKYOU ENOUGH

sorry for screaming but i meant it

Isabella

Originally Posted by Rizky
Perhaps something like this, using array formulas (means when ENTER you need to press CTRL-SHIFT-ENTER button together)

9. A better way would be to drop the table into Power Query, unpivot the date columns, set the date column header to Date and the data type to Date, the leave column to Leave Type, and load the transformed table into Excel. Then pivot that table, with Team in in rows, Date in columns, and count of Leave Type in Values. Group the dates by Months and Years, add a slicer for Leave Type, and you have the most flexible solution you could imagine.

10. Hello Bob,

im im appreciate your response- it's a foreign language to to me :-), I will however give your instructions a try, if there is anything between these steps however I will fail lol

iss

Originally Posted by Bob Phillips
A better way would be to drop the table into Power Query, unpivot the date columns, set the date column header to Date and the data type to Date, the leave column to Leave Type, and load the transformed table into Excel. Then pivot that table, with Team in in rows, Date in columns, and count of Leave Type in Values. Group the dates by Months and Years, add a slicer for Leave Type, and you have the most flexible solution you could imagine.

Page 1 of 2 1 2 Last

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•