Countif - Two Criteria in two columns are met.
 

I am attempting to create a formula which returns the number of times a
value which occurs in G and is above certain values in F is presents.

Ie I have in Column F the outstanding balance of the client.
in column G the country they are in.

There are two ways I can see possible to get the output I wish being a
table which shows each country as Y and a range of debt as X e.g.
$500 $100 $50 $10
Albania x x x x
Cyprus x x x x
Malta x x x x

Now; I would of assumed that I can use COUNTIF but that is stressing me
out to no end, I have looked at PivotTables, which would be great if I
could create custom columns. (Can you do that?????)

So basicalli if I want to know how many debtors there are in Cyprus
$500 what would the formula be, ?


Excellent.

Thanks


--
samprince
------------------------------------------------------------------------
samprince's Profile: http://www.excelforum.com/member.php...o&userid=34168
View this thread: http://www.excelforum.com/showthread...hreadid=556036

Countif - Two Criteria in two columns are met.
 

=COUNTIF(G:G,H:H)

as shown in your example.

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

"samprince" wrote
in message ...

I am attempting to create a formula which returns the number of times a
value which occurs in G and is above certain values in F is presents.

Ie I have in Column F the outstanding balance of the client.
in column G the country they are in.

There are two ways I can see possible to get the output I wish being a
table which shows each country as Y and a range of debt as X e.g.
$500 $100 $50 $10
Albania x x x x
Cyprus x x x x
Malta x x x x

Now; I would of assumed that I can use COUNTIF but that is stressing me
out to no end, I have looked at PivotTables, which would be great if I
could create custom columns. (Can you do that?????)

So basicalli if I want to know how many debtors there are in Cyprus
$500 what would the formula be, ?


Excellent.

Thanks


--
samprince
------------------------------------------------------------------------
samprince's Profile:
http://www.excelforum.com/member.php...o&userid=34168
View this thread: http://www.excelforum.com/showthread...hreadid=556036

Countif - Two Criteria in two columns are met.
 

=SUMIF(G:G,"Albania",H:H)

as shown in your example

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

"samprince" wrote
in message ...

I am attempting to create a formula which returns the number of times a
value which occurs in G and is above certain values in F is presents.

Ie I have in Column F the outstanding balance of the client.
in column G the country they are in.

There are two ways I can see possible to get the output I wish being a
table which shows each country as Y and a range of debt as X e.g.
$500 $100 $50 $10
Albania x x x x
Cyprus x x x x
Malta x x x x

Now; I would of assumed that I can use COUNTIF but that is stressing me
out to no end, I have looked at PivotTables, which would be great if I
could create custom columns. (Can you do that?????)

So basicalli if I want to know how many debtors there are in Cyprus
$500 what would the formula be, ?


Excellent.

Thanks


--
samprince
------------------------------------------------------------------------
samprince's Profile:
http://www.excelforum.com/member.php...o&userid=34168
View this thread: http://www.excelforum.com/showthread...hreadid=556036

Countif - Two Criteria in two columns are met.
 

Have you tried using an inequality in your expression?

=countif(b2:z2,"500")

HTH
Owen

Bob Phillips wrote:
=COUNTIF(G:G,H:H)

as shown in your example.

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

"samprince" wrote
in message ...

I am attempting to create a formula which returns the number of times a
value which occurs in G and is above certain values in F is presents.

Ie I have in Column F the outstanding balance of the client.
in column G the country they are in.

There are two ways I can see possible to get the output I wish being a
table which shows each country as Y and a range of debt as X e.g.
$500 $100 $50 $10
Albania x x x x
Cyprus x x x x
Malta x x x x

Now; I would of assumed that I can use COUNTIF but that is stressing me
out to no end, I have looked at PivotTables, which would be great if I
could create custom columns. (Can you do that?????)

So basicalli if I want to know how many debtors there are in Cyprus
$500 what would the formula be, ?


Excellent.

Thanks


--
samprince
------------------------------------------------------------------------
samprince's Profile:
http://www.excelforum.com/member.php...o&userid=34168
View this thread: http://www.excelforum.com/showthread...hreadid=556036

Countif - Two Criteria in two columns are met.
 

I need the forumula to return the fequency of occurences as opposed to
the Sum of all the occurences [=

that seems to be the hard part.


--
samprince
------------------------------------------------------------------------
samprince's Profile: http://www.excelforum.com/member.php...o&userid=34168
View this thread: http://www.excelforum.com/showthread...hreadid=556036

Countif - Two Criteria in two columns are met.
 

Isn't that what are responses gave?

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

"samprince" wrote
in message ...

I need the forumula to return the fequency of occurences as opposed to
the Sum of all the occurences [=

that seems to be the hard part.


--
samprince
------------------------------------------------------------------------
samprince's Profile:
http://www.excelforum.com/member.php...o&userid=34168
View this thread: http://www.excelforum.com/showthread...hreadid=556036

Countif - Two Criteria in two columns are met.
 

=Sumif adds together the value of the occurences

\- :

I need the frequency. i.e a =Countif forumula.


--
samprince
------------------------------------------------------------------------
samprince's Profile: http://www.excelforum.com/member.php...o&userid=34168
View this thread: http://www.excelforum.com/showthread...hreadid=556036

Countif - Two Criteria in two columns are met.
 

Both Owen and I gave COUNTIF solutions.

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

"samprince" wrote
in message ...

=Sumif adds together the value of the occurences

\- :

I need the frequency. i.e a =Countif forumula.


--
samprince
------------------------------------------------------------------------
samprince's Profile:
http://www.excelforum.com/member.php...o&userid=34168
View this thread: http://www.excelforum.com/showthread...hreadid=556036

Countif - Two Criteria in two columns are met.
 

I saw,

Though the formula given would of return the number of times anything
occured and 500 ,,

I needed for instance when 500 occured in one column at the same time
Albania occurred in the next.

However I have worked it out using IF.

=IF((AND($G9="Albania",$F9500)),1,0)

Then Sum the 1's
I can get groups also now by using
Sum of all =IF((AND($G9="Albania",$F91000)),1,0)
- minus -
Sum of all =IF((AND($G9="Albania",$F9500)),1,0)

Equal all those Between $500 & $999.99

Thanks for your help.
SP.


--
samprince
------------------------------------------------------------------------
samprince's Profile: http://www.excelforum.com/member.php...o&userid=34168
View this thread: http://www.excelforum.com/showthread...hreadid=556036

Countif - Two Criteria in two columns are met.
 

=sumproduct((f2:f10="Albania")*(g2:g10500)*(g2:g1 0<=1000))

Regards

Dav


--
Dav
------------------------------------------------------------------------
Dav's Profile: http://www.excelforum.com/member.php...o&userid=27107
View this thread: http://www.excelforum.com/showthread...hreadid=556036

Countif - Two Criteria in two columns are met.
 

But your $500 values are all in one column, so you could use

=COUNTIF($A:$A,"Albania",B:B)

and then just change to C:C for $50 values etc.

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

"samprince" wrote
in message ...

I saw,

Though the formula given would of return the number of times anything
occured and 500 ,,

I needed for instance when 500 occured in one column at the same time
Albania occurred in the next.

However I have worked it out using IF.

=IF((AND($G9="Albania",$F9500)),1,0)

Then Sum the 1's
I can get groups also now by using
Sum of all =IF((AND($G9="Albania",$F91000)),1,0)
- minus -
Sum of all =IF((AND($G9="Albania",$F9500)),1,0)

Equal all those Between $500 & $999.99

Thanks for your help.
SP.


--
samprince
------------------------------------------------------------------------
samprince's Profile:
http://www.excelforum.com/member.php...o&userid=34168
View this thread: http://www.excelforum.com/showthread...hreadid=556036

Countif - Two Criteria in two columns are met.
 

Dav Wrote:
=sumproduct((f2:f10="Albania")*(g2:g10500)*(g2:g1 0<=1000))

Regards

Dav

Also works [=


--
samprince
------------------------------------------------------------------------
samprince's Profile: http://www.excelforum.com/member.php...o&userid=34168
View this thread: http://www.excelforum.com/showthread...hreadid=556036