Countif - Two Criteria in two columns are met.

[samprince]

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

[Bob Phillips]

=COUNTIF(G:G,H:H)

as shown in your example.

--
HTH

Bob Phillips

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

"samprince" <[email protected]> wrote
in message news:[email protected]...
>
> 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
>

[Bob Phillips]

=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" <[email protected]> wrote
in message news:[email protected]...
>
> 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
>

[Owen]

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" <[email protected]> wrote
> in message news:[email protected]...
> >
> > 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
> >

[samprince]

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.

[Bob Phillips]

Isn't that what are responses gave?

--
HTH

Bob Phillips

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

"samprince" <[email protected]> wrote
in message news:[email protected]...
>
> 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
>

[samprince]

=Sumif adds together the value of the occurences

\- :

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

[Bob Phillips]

Both Owen and I gave COUNTIF solutions.

--
HTH

Bob Phillips

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

"samprince" <[email protected]> wrote
in message news:[email protected]...
>
> =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
>

[samprince]

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",$F9>500)),1,0)

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

Equal all those Between $500 & $999.99

Thanks for your help.
SP.

[Dav]

=sumproduct((f2:f10="Albania")*(g2:g10>500)*(g2:g10<=1000))

Regards

Dav

[Bob Phillips]

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" <[email protected]> wrote
in message news:[email protected]...
>
> 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",$F9>500)),1,0)
>
> Then Sum the 1's
> I can get groups also now by using
> Sum of all =IF((AND($G9="Albania",$F9>1000)),1,0)
> - minus -
> Sum of all =IF((AND($G9="Albania",$F9>500)),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
>

[Bob Phillips]

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" <[email protected]> wrote
in message news:[email protected]...
>
> 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",$F9>500)),1,0)
>
> Then Sum the 1's
> I can get groups also now by using
> Sum of all =IF((AND($G9="Albania",$F9>1000)),1,0)
> - minus -
> Sum of all =IF((AND($G9="Albania",$F9>500)),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
>

[samprince]

=sumproduct((f2:f10="Albania")*(g2:g10>500)*(g2:g10<=1000))

Regards

Dav

Also works [=