Counting information in one column based on criteria in another

[bwpscmw]

Hi

Can you help with the formula that I would need to count the number of occurances there are in columns A, B and C but only if the data in column D for that row is 15 or 30 (I hope that makes sense!)

A	B	C	D
3	3	3	15
	3		30
3	3	3
		3

<tbody>
</tbody>

Many thanks

 

[Fluff]

Hi & welcome to MrExcel.
How about

Excel 2013/2016
	A	B	C	D	E
1	A	B	C	D
2	3	3	3	15	3
3		3		30	1
4	3	3	3	10
5			3
Index

Cell Formulas
Range	Formula
E2	=IF(OR(D2={15,30}),COUNTA(A2:C2),"")

 

[bwpscmw]

Hmmm I need to count the occurances in each column (not each row). When I tried to adapt your formula to accommodate that, it didn't work.

Excel 2013/2016

	A	B	C	D	E
1	A	B	C	D
2	3	3	3	15	3
3		3		30	1
4	3	3	3	10
5			3

<colgroup><col><col><col><col><col><col></colgroup><thead>
</thead><tbody>
</tbody>

So what I am looking for is to find out how many occurances in column A meet the criteria of 15 or 30 in column D.

Is it possible?

Thanks

 

[Fluff]

In that case can you please post some data showing the expected results

 

[bwpscmw]

In that case can you please post some data showing the expected results

Child	Mon	Tues	Wed	Thurs	Funding
A	3	3			15
B	3	3		3
C	3.25		3.25		30
D		3	3	3	15
E	3		3	3
Total	2	2	2	1

<tbody>
</tbody>

Hope this helps. So I want the formula to tell me how many children with funding are attending each day (the 3/3.25 indicates how many hours they attend which I need for other purposes on the spreadsheet).

Does that make sense?

Many thanks

 

[Marcelo Branco]

Try

	A​	B​	C​	D​	E​	F​
1​	Child​	Mon​	Tues​	Wed​	Thurs​	Funding​
2​	A​	3​	3​			15​
3​	B​	3​	3​		3​
4​	C​	3,25​		3.25​		30​
5​	D​		3​	3​	3​	15​
6​	E​	3​		3​	3​
7​	Total​	2​	2​	2​	1​

<tbody>
</tbody>

Formula in B7 copied across
=SUM(COUNTIFS(B$2:B$6,">0",$F$2:$F$6,{15;30}))

M.

 

[bwpscmw]

Try

	A​	B​	C​	D​	E​	F​
1​	Child​	Mon​	Tues​	Wed​	Thurs​	Funding​
2​	A​	3​	3​			15​
3​	B​	3​	3​		3​
4​	C​	3,25​		3.25​		30​
5​	D​		3​	3​	3​	15​
6​	E​	3​		3​	3​
7​	Total​	2​	2​	2​	1​

<tbody>
</tbody>

Formula in B7 copied across
=SUM(COUNTIFS(B$2:B$6,">0",$F$2:$F$6,{15;30}))

M.

Awesome, this seems to have done the trick. Many thanks!