COUNT IF

[meganruth]

Hi All,

I'm trying to input a formula based on multiple criteria,

Firstly I need it to only count if Column G shows a FT or HT
secondly i need it to count PT recorded between columns H and M for each row, BUT only if there is NOT a HT or FT present in any of those cells. i need both criteria's to link and give me a figure if all of the above conditions are met

am i asking for too much? this one is really testing my patience

 

[Sufiyan97]

Hi & Welcome to MrExcel.

Can you post the data along with some manually typed expected results.

MrExcel has a tool called “XL2BB” that lets you post samples of your data that will allow us to copy/paste it to our Excel spreadsheets, so we can work with the same copy of data that you are. Instructions on using this tool can be found here: XL2BB Add-in

Note that there is also a "Test Here” forum on this board. This is a place where you can test using this tool (or any other posting techniques that you want to test) before trying to use those tools in your actual posts.

 

[steve the fish]

Hello. That picture is tiny! Paste some sample data here rather than a picture. A picture means we need to type out to test formulas. Less likely to receive help.

 

[meganruth]

Hello. That picture is tiny! Paste some sample data here rather than a picture. A picture means we need to type out to test formulas. Less likely to receive help.

Period	Week number (calendar year)	Portion (of week in estab)	Portion (denominator)	Overall classification (CU172)	Session counted (numerator)	Industries classification (CU172a)	Education classification (CU172b)	Services classification (CU172c)	Interventions classification (CU172d)	Other purposeful classification (CU172e)
Jan 2023​	2023-W01	1.00​	1.00​	FT	1​	Unemployed	Unemployed	Unemployed	Unemployed	FT
Jan 2023​	2023-W01	1.00​	1.00​	FT	1​	Unemployed	Unemployed	Unemployed	Unemployed	FT
Jan 2023​	2023-W01	1.00​	1.00​	PT	0​	PT	Unemployed	Unemployed	Unemployed	Unemployed
Jan 2023​	2023-W01	1.00​	1.00​	HT	1​	PT	HT	Unemployed	Unemployed	PT
Jan 2023​	2023-W01	1.00​	1.00​	FT	1​	Unemployed	FT	Unemployed	Unemployed	Unemployed
Jan 2023​	2023-W01	1.00​	1.00​	PT	0​	PT	Unemployed	Unemployed	Unemployed	Unemployed
Jan 2023​	2023-W01	1.00​	1.00​	FT	1​	Unemployed	Unemployed	FT	Unemployed	Unemployed
Jan 2023​	2023-W01	1.00​	1.00​	HT	1​	Unemployed	Unemployed	HT	Unemployed	Unemployed
Jan 2023​	2023-W01	1.00​	1.00​	FT	1​	Unemployed	Unemployed	FT	Unemployed	Unemployed
Jan 2023​	2023-W01	1.00​	1.00​	FT	1​	Unemployed	Unemployed	Unemployed	Unemployed	FT
Jan 2023​	2023-W01	1.00​	1.00​	HT	1​	PT	Unemployed	Unemployed	Unemployed	PT

 

[meganruth]

Hello. That picture is tiny! Paste some sample data here rather than a picture. A picture means we need to type out to test formulas. Less likely to receive help.

Thanks Steve, sorry new to this!

from the data, i want to be able to count the amount of PT between industries and other based on if the overall column shows FT or HT. in addition if any of the columns between industries show FT or HT i do not want to count

 

[meganruth]

Thanks Steve, sorry new to this!

from the data, i want to be able to count the amount of PT between industries and other based on if the overall column shows FT or HT. in addition if any of the columns between industries show FT or HT i do not want to count

For example, the last row would be 2. but row 4 would be 0 because there is a HT present

 

[meganruth]

a

Hi & Welcome to MrExcel.

Can you post the data along with some manually typed expected results.

MrExcel has a tool called “XL2BB” that lets you post samples of your data that will allow us to copy/paste it to our Excel spreadsheets, so we can work with the same copy of data that you are. Instructions on using this tool can be found here: XL2BB Add-in

Note that there is also a "Test Here” forum on this board. This is a place where you can test using this tool (or any other posting techniques that you want to test) before trying to use those tools in your actual posts.

Sorry i am unable to download the tool due to restrictions on my work computer. I have uploaded data below, and my expected results

Thanks

 

[steve the fish]

Try this:

=IF(SUM(COUNTIF(G2:M2,{"FT","HT"})),"",COUNTIF(H2:M2,"PT"))

 

[steve the fish]

Why would last row be 2?? Oh sorry misread! Hold on.

 

[steve the fish]

=IF(AND(SUM(COUNTIF(G2,{"FT","HT"}))=1,SUM(COUNTIF(H2:M2,{"FT","HT"}))=0),COUNTIF(H2:M2,"PT"),"")

This work?