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

I

Isabellalearning

New member
Joined
Oct 23, 2014
Messages
7
Reaction score
0
Points
0
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
 

Attachments

  • sample test.xlsx
    34.8 KB · Views: 17
=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
 
Last edited:
I am thinking he might want an overall summary

=SUMPRODUCT(($B$2:$B$14="Team 3")*($C$2:$JU$14="AL"))
 
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

WizzardOfOz said:
=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
Click to expand...
 
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
 
Perhaps something like this, using array formulas (means when ENTER you need to press CTRL-SHIFT-ENTER button together)
 

Attachments

  • sample test.xlsx
    37.6 KB · Views: 21
Omg

YOUR AMAZING!!!
I CANT THANKYOU ENOUGH

sorry for screaming but i meant it

Isabella


Rizky said:
Perhaps something like this, using array formulas (means when ENTER you need to press CTRL-SHIFT-ENTER button together)
Click to expand...
 
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.
 
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

Bob Phillips said:
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.
Click to expand...
 
Well, assuming that you have Excel 2010, first you will have to download and install Power Query, http://www.microsoft.com/en-us/download/details.aspx?id=39379.

Basically, it is just creating a pivot table with a slicer, but your data is not in a god format to pivot, so you need Power Query to get into a nice pivottable table.

I can post an example workbook if you like.
 
You must log in or register to reply here.
Back
Top