countifs for numbers including and between based on multiple criteria

R

Rameses

Board Regular
Joined
Mar 3, 2010
Messages
137
Hi all
Its been a long time since I was last here.

I have a school governors meeting tomorrow and need to crunch some data urgently

Column B = Gender ie. B or G
Column AX = Reading Points (recorded as numbers up to 1 decimal place)

Data goes from Row 4 to Row 58


I wish to count 2 things separately

a) To be recorded in B74 > The number of boys "B" who have scored between and including 4 and 5 Reading points
b) To be recorded in B75 > The number of Boys "B" who have scored between 4 and 9 Reading points

for a) I have used the formula
=SUM((G4:G58,"B")*(AX4:AX58,>=4)*(AX4:AX58,<=5)) followed by Ctl+Shift+Enter

The above doesnt work

Any suggestions gratefully accepted as this is for 14 hours! (I should have done it earlier > kicking myself!!)

Rameses
 
Sort by date Sort by votes
Rameses said:
Hi all
Its been a long time since I was last here.

I have a school governors meeting tomorrow and need to crunch some data urgently

Column B = Gender ie. B or G
Column AX = Reading Points (recorded as numbers up to 1 decimal place)

Data goes from Row 4 to Row 58


I wish to count 2 things separately

a) To be recorded in B74 > The number of boys "B" who have scored between and including 4 and 5 Reading points
b) To be recorded in B75 > The number of Boys "B" who have scored between 4 and 9 Reading points

for a) I have used the formula
=SUM((G4:G58,"B")*(AX4:AX58,>=4)*(AX4:AX58,<=5)) followed by Ctl+Shift+Enter

The above doesnt work

Any suggestions gratefully accepted as this is for 14 hours! (I should have done it earlier > kicking myself!!)

Rameses
Click to expand...

Does this help:
Excel 2010
EFGARASATAUAVAWAX
B
B
B
B
B
B
B
B
B

<tbody>
[TD="align: center"]4[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

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

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

[TD="align: center"]6[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"][/TD]

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

</tbody>
Sheet1​

[TABLE="width: 85%"]
<tbody>[TR]
[TD]Worksheet Formulas[TABLE="width: 100%"]
<tbody>[TR="bgcolor: #DAE7F5"]
[TH]Cell[/TH]
[TH="align: left"]Formula[/TH]
[/TR]
[TR]
[TH="bgcolor: #DAE7F5"]E5[/TH]
[TD="align: left"]=SUMPRODUCT((G4:G58="B")*((AX4:AX58>=4)*(AX4:AX58<=5)))[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]

[TABLE="width: 85%"]
<tbody>[TR]
[TD]Array Formulas[TABLE="width: 100%"]
<tbody>[TR="bgcolor: #DAE7F5"]
[TH]Cell[/TH]
[TH="align: left"]Formula[/TH]
[/TR]
[TR]
[TH="bgcolor: #DAE7F5"]E6[/TH]
[TD="align: left"]{=SUM((G4:G58="B")*(AX4:AX58>=4)*(AX4:AX58<=5))}[/TD]
[/TR]
</tbody>[/TABLE]
Entered with Ctrl+Shift+Enter. If entered correctly, Excel will surround with curly braces {}.
Note: Do not try and enter the {} manually yourself[/TD]
[/TR]
</tbody>[/TABLE]

But the SUMPRODUCT is the prefer one.

Excel 07/10:
=COUNTIFS(G4:G58,"B",AX4:AX58,">=4",AX4:AX58,"<=5")
 
Last edited:
Upvote 0
Excel Workbook
B
741
7515
Sheet


Normally entered.
You said B or G was to be found in colun B, adjust the formulae if they're to be found elsewhere.
Since you don't say which version of Excel you're using I've used a formula which will work in xl2000 up.
 
Last edited:
Upvote 0
Thats works fab. I now have to add in a further detailed count >

> where I now have to count the no. of Boys with a "Y" for Free School Meals who have scored between and including 4 and 5 Reading points.

where

Column B = Gender ie. B or G
Column C is FSM
Column AX = Reading Points (recorded as numbers up to 1 decimal place)

I have tried reproducing the formulas to work it but cant!

A worksheet formula would be preferred if possible?

many thanks

rameses
 
Upvote 0
Rameses said:
> where I now have to count the no. of Boys with a "Y" for Free School Meals who have scored between and including 4 and 5 Reading points.

where

Column B = Gender ie. B or G
Column C is FSM
Column AX = Reading Points (recorded as numbers up to 1 decimal place)
Click to expand...

You can add any number of conditions with COUNTIFS so try this version

=COUNTIFS(B4:B58,"B",C4:C58,"Y",AX4:AX58,">=4",AX4:AX58,"<=5")
 
Upvote 0
barry houdini said:
You can add any number of conditions with COUNTIFS so try this version

=COUNTIFS(B4:B58,"B",C4:C58,"Y",AX4:AX58,">=4",AX4:AX58,"<=5")
Click to expand...

Hi barry

thank you it works but it appears to double count > I think it is to do with the numbers ie less than 4 but equal to 5 >do i need an array to go through data line by line?
 
Upvote 0
Hi Barry

Opps my mistake > who would have thought that Lemmy is a girls name!

Thank you so much for your help on this as this will help me get finished in time to present!

Rameses
 
Upvote 0
Hi all

I have an extension until tomorrow and am stuck yet again!!!

Column B = Gender ie. B or G
Column E = Ethnicity (WROM/BAFR/WBRI/WEEU/BLCA)
Column AX = Reading Points (recorded as numbers up to 1 decimal place)
Rows are 4:65

I want to count the number of Boys who are BAFR and BLCA who have between 4 and 5 reading points
Ideally require a worksheet formula as a Countif

any help greatly appreciated especially as this is the last formula I need to complete!

many thanks

Rameses
 
Upvote 0
Rameses said:
Hi all

I have an extension until tomorrow and am stuck yet again!!!

Column B = Gender ie. B or G
Column E = Ethnicity (WROM/BAFR/WBRI/WEEU/BLCA)
Column AX = Reading Points (recorded as numbers up to 1 decimal place)
Rows are 4:65

I want to count the number of Boys who are BAFR and BLCA who have between 4 and 5 reading points
Ideally require a worksheet formula as a Countif

any help greatly appreciated especially as this is the last formula I need to complete!

many thanks

Rameses
Click to expand...
Maybe something like this...

=COUNTIFS(B4:B65,"B",C4:C65,"B*",AX4:AX65,">=4",AX4:AX65,"<=5")
 
Upvote 0
You must log in or register to reply here.

Similar threads

C
  • Question Question
Create a new row for every date in row and create multiple copies of data to new worksheet based on cell number value for each date in the row VBA
Replies
0
Views
201
Chris258
C
T
  • Question Question
cross country meet scoring
Replies
7
Views
1K
Joe4
Joe4
L
  • Solved
SUMIFS based on multiple criteria and between date
2
Replies
17
Views
1K
etaf
etaf
GMC The Macro Man
  • Question Question
Count with Multiple Criteria - Follow up
Replies
2
Views
270
myall_blues
myall_blues
F
  • Question Question
Looking to index some data based on multiple criteria, then index further data based on the 1st indexed data results
2
Replies
13
Views
973
KRice
K

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top