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!)[TABLE="width: 500"]
<tbody>[TR]
[TD]A[/TD]
[TD]B[/TD]
[TD]C[/TD]
[TD]D[/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]3[/TD]
[TD]3[/TD]
[TD]15[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]3[/TD]
[TD][/TD]
[TD]30[/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]3[/TD]
[TD]3[/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD]3[/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

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

<colgroup><col><col><col><col><col><col></colgroup><thead>
[TH="align: center"][/TH]
[TH="align: center"]A[/TH]
[TH="align: center"]B[/TH]
[TH="align: center"]C[/TH]
[TH="align: center"]D[/TH]
[TH="align: center"]E[/TH]

</thead><tbody>
[TD="align: center"]1[/TD]

[TD="align: right"][/TD]

[TD="align: center"]2[/TD]
[TD="align: right"]3[/TD]
[TD="align: right"]3[/TD]
[TD="align: right"]3[/TD]
[TD="align: right"]15[/TD]
[TD="align: right"]3[/TD]

[TD="align: center"]3[/TD]
[TD="align: right"][/TD]
[TD="align: right"]3[/TD]
[TD="align: right"][/TD]
[TD="align: right"]30[/TD]
[TD="align: right"]1[/TD]

[TD="align: center"]4[/TD]
[TD="align: right"]3[/TD]
[TD="align: right"]3[/TD]
[TD="align: right"]3[/TD]
[TD="align: right"]10[/TD]

[TD="align: center"]5[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"]3[/TD]
[TD="align: right"][/TD]

</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

[TABLE="width: 500"]
<tbody>[TR]
[TD]Child[/TD]
[TD]Mon[/TD]
[TD]Tues[/TD]
[TD]Wed[/TD]
[TD]Thurs[/TD]
[TD]Funding[/TD]
[/TR]
[TR]
[TD]A
[/TD]
[TD]3[/TD]
[TD]3[/TD]
[TD][/TD]
[TD][/TD]
[TD]15[/TD]
[/TR]
[TR]
[TD]B[/TD]
[TD]3[/TD]
[TD]3[/TD]
[TD][/TD]
[TD]3[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]C[/TD]
[TD]3.25[/TD]
[TD][/TD]
[TD]3.25[/TD]
[TD][/TD]
[TD]30[/TD]
[/TR]
[TR]
[TD]D[/TD]
[TD][/TD]
[TD]3[/TD]
[TD]3[/TD]
[TD]3[/TD]
[TD]15[/TD]
[/TR]
[TR]
[TD]E[/TD]
[TD]3[/TD]
[TD][/TD]
[TD]3[/TD]
[TD]3[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Total[/TD]
[TD]2[/TD]
[TD]2[/TD]
[TD]2[/TD]
[TD]1[/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

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


[TABLE="class: grid"]
<tbody>[TR]
[TD="bgcolor: #DCE6F1"][/TD]
[TD="bgcolor: #DCE6F1"]

A​

[/TD]
[TD="bgcolor: #DCE6F1"]

B​

[/TD]
[TD="bgcolor: #DCE6F1"]

C​

[/TD]
[TD="bgcolor: #DCE6F1"]

D​

[/TD]
[TD="bgcolor: #DCE6F1"]

E​

[/TD]
[TD="bgcolor: #DCE6F1"]

F​

[/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

1​

[/TD]
[TD]

Child​

[/TD]
[TD]

Mon​

[/TD]
[TD]

Tues​

[/TD]
[TD]

Wed​

[/TD]
[TD]

Thurs​

[/TD]
[TD]

Funding​

[/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

2​

[/TD]
[TD]

A​

[/TD]
[TD]

3​

[/TD]
[TD]

3​

[/TD]
[TD][/TD]
[TD][/TD]
[TD]

15​

[/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

3​

[/TD]
[TD]

B​

[/TD]
[TD]

3​

[/TD]
[TD]

3​

[/TD]
[TD][/TD]
[TD]

3​

[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

4​

[/TD]
[TD]

C​

[/TD]
[TD]

3,25​

[/TD]
[TD][/TD]
[TD]

3.25​

[/TD]
[TD][/TD]
[TD]

30​

[/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

5​

[/TD]
[TD]

D​

[/TD]
[TD][/TD]
[TD]

3​

[/TD]
[TD]

3​

[/TD]
[TD]

3​

[/TD]
[TD]

15​

[/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

6​

[/TD]
[TD]

E​

[/TD]
[TD]

3​

[/TD]
[TD][/TD]
[TD]

3​

[/TD]
[TD]

3​

[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

7​

[/TD]
[TD]

Total​

[/TD]
[TD]

2​

[/TD]
[TD]

2​

[/TD]
[TD]

2​

[/TD]
[TD]

1​

[/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]


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

M.

 

[bwpscmw]

Try


[TABLE="class: grid"]
<tbody>[TR]
[TD="bgcolor: #DCE6F1"][/TD]
[TD="bgcolor: #DCE6F1"]

A​

[/TD]
[TD="bgcolor: #DCE6F1"]

B​

[/TD]
[TD="bgcolor: #DCE6F1"]

C​

[/TD]
[TD="bgcolor: #DCE6F1"]

D​

[/TD]
[TD="bgcolor: #DCE6F1"]

E​

[/TD]
[TD="bgcolor: #DCE6F1"]

F​

[/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

1​

[/TD]
[TD]

Child​

[/TD]
[TD]

Mon​

[/TD]
[TD]

Tues​

[/TD]
[TD]

Wed​

[/TD]
[TD]

Thurs​

[/TD]
[TD]

Funding​

[/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

2​

[/TD]
[TD]

A​

[/TD]
[TD]

3​

[/TD]
[TD]

3​

[/TD]
[TD][/TD]
[TD][/TD]
[TD]

15​

[/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

3​

[/TD]
[TD]

B​

[/TD]
[TD]

3​

[/TD]
[TD]

3​

[/TD]
[TD][/TD]
[TD]

3​

[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

4​

[/TD]
[TD]

C​

[/TD]
[TD]

3,25​

[/TD]
[TD][/TD]
[TD]

3.25​

[/TD]
[TD][/TD]
[TD]

30​

[/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

5​

[/TD]
[TD]

D​

[/TD]
[TD][/TD]
[TD]

3​

[/TD]
[TD]

3​

[/TD]
[TD]

3​

[/TD]
[TD]

15​

[/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

6​

[/TD]
[TD]

E​

[/TD]
[TD]

3​

[/TD]
[TD][/TD]
[TD]

3​

[/TD]
[TD]

3​

[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

7​

[/TD]
[TD]

Total​

[/TD]
[TD]

2​

[/TD]
[TD]

2​

[/TD]
[TD]

2​

[/TD]
[TD]

1​

[/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]


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!