Excel Formula conditional IF and Count and a range

[salallan]

Hi,

I want to anylise some data in excel, the data comes from a dump from a database

Region, Days open, Ticket Number

I am try to create a formula that goes and counts the number of instance in each region so.

I will have multiple entries

Region, Days open, Ticket Number
America 10 tick1
America 15 tick2
UK 5 tick3
Asia 20 tick4
America 7 tick5
UK 2 tick9


The formula or condition would populate my table based on the condition below,

count the number of instances where its greater then 10 but less then or equal to 20, and return me the count for each region

America 2
UK 0
Asia 1

Please could some help

I have had a look at arrays conditional sums and if statements but cannot get it work.

 

[VoG]

Try like this

Excel Workbook
	A	B	C	D	E	F
1	Region,	Days	Ticket		America	2
2	America	10	tick1		UK	0
3	America	15	tick2		Asia	1
4	UK	5	tick3
5	Asia	20	tick4
6	America	7	tick5
7	UK	2	tick9
Sheet6

 

[salallan]

You are a Gentleman and a star,

Thank you just what I was looking for, the formula works perfect.

 

[bosco_yip]

If your specification is :

Days >10 and <=20

then, the result will be :

America….1
UK…………..0
Asia…….….1

Not,

America….2
UK…………..0
Asia…….….1

Formula in F1,

=SUMPRODUCT(--(A$2:A$7=E1),--(B$2:B$7>10),--(B$2:B$7<=20))

or,

=SUM(INDEX((A$2:A$7=E1)*(B$2:B$7>10)*(B$2:B$7<=20),))

Regards

 

[salallan]

tried that it also works, I wanted to add a bit more complication to the formula,

the data will look as follows


Region, day, ticket, team

America 10 tick1 team1
America 15 tick2 team2
UK 5 tick3 team3
ASIA 20 tick4 team4
America 7 tick5 team2
America 15 tick6 team1
UK 2 tick7 team3
America 17 tick8 team9

so the forumla is the same but with added extra condition

results will be for >=10 <=20, Region = America, team=1 or team2
>=10 <=20, Region = Asia, team=4 or team5

So my results will be
America 3
Asia 1

and so on, any further help is very much appricated, if someone can assist today.

thank you all

 

[bosco_yip]

1] Column D for the name of : team1, team2………

2] For conditions : >=10 <=20, Region = America, team=1 or team2

2.1] Formula :

=SUMPRODUCT(--(A$2:A$9="America"),--(B$2:B$9>=10),--(B$2:B$9<=20),((D$2:D$9="team1")+(D$2:D$9="team2")))

Or,

=SUM(INDEX((A$2:A$9="America")*(B$2:B$9>=10)*(B$2:B$9<=20)*((D$2:D$9="team1")+(D$2:D$9="team2")),))


3] For conditions : >=10 <=20, Region = Asia team=4 or team5

3.1] Formula :

=SUMPRODUCT(--(A$2:A$9="Asia"),--(B$2:B$9>=10),--(B$2:B$9<=20),((D$2:D$9="team4")+(D$2:D$9="team5")))

Or,

=SUM(INDEX((A$2:A$9="Asia")*(B$2:B$9>=10)*(B$2:B$9<=20)*((D$2:D$9="team4")+(D$2:D$9="team5")),))

Regards