Referencing previous column in COUNTIF

[[email protected]]

Evening All,

I am a novice Excel user, so please excuse the probably very basic
question. I have a spreadsheet with two columns, e.g.

A E
D E
F L
A E
G E

I am trying to get some totals for column 2 based on the value, so I
used COUNTIF(D4:D34, "E"). This is great, but I only want to count
column 2 if the corresponding cell in column 1 is "A" (or not "A" for
my other total). I think I want to somehow use INDIRECT as well, but
can't quite figure out how! The total I am looking for in the example
would be 2 (col2="E" and col1="A").

Thanks for any hints/advice.

Cheers,
Al.

[Bob Phillips]

=SUMPRODUCT(--(A1:A10="A"),--(B1:B10-"E"))

--

HTH

RP
(remove nothere from the email address if mailing direct)


<[email protected]> wrote in message
news:[email protected]...
> Evening All,
>
> I am a novice Excel user, so please excuse the probably very basic
> question. I have a spreadsheet with two columns, e.g.
>
> A E
> D E
> F L
> A E
> G E
>
> I am trying to get some totals for column 2 based on the value, so I
> used COUNTIF(D4:D34, "E"). This is great, but I only want to count
> column 2 if the corresponding cell in column 1 is "A" (or not "A" for
> my other total). I think I want to somehow use INDIRECT as well, but
> can't quite figure out how! The total I am looking for in the example
> would be 2 (col2="E" and col1="A").
>
> Thanks for any hints/advice.
>
> Cheers,
> Al.
>

[Jambruins]

Try this:

=countif(D4:D34, "E") + countif(A4:A34, "A")



"[email protected]" wrote:

> Evening All,
>
> I am a novice Excel user, so please excuse the probably very basic
> question. I have a spreadsheet with two columns, e.g.
>
> A E
> D E
> F L
> A E
> G E
>
> I am trying to get some totals for column 2 based on the value, so I
> used COUNTIF(D4:D34, "E"). This is great, but I only want to count
> column 2 if the corresponding cell in column 1 is "A" (or not "A" for
> my other total). I think I want to somehow use INDIRECT as well, but
> can't quite figure out how! The total I am looking for in the example
> would be 2 (col2="E" and col1="A").
>
> Thanks for any hints/advice.
>
> Cheers,
> Al.
>
>

[Peo Sjoblom]

=SUMPRODUCT(--(A2:A100="A"),--(B2:B100="E"))

will count "E" in B where A is "A"


--

Regards,

Peo Sjoblom


<[email protected]> wrote in message
news:[email protected]...
> Evening All,
>
> I am a novice Excel user, so please excuse the probably very basic
> question. I have a spreadsheet with two columns, e.g.
>
> A E
> D E
> F L
> A E
> G E
>
> I am trying to get some totals for column 2 based on the value, so I
> used COUNTIF(D4:D34, "E"). This is great, but I only want to count
> column 2 if the corresponding cell in column 1 is "A" (or not "A" for
> my other total). I think I want to somehow use INDIRECT as well, but
> can't quite figure out how! The total I am looking for in the example
> would be 2 (col2="E" and col1="A").
>
> Thanks for any hints/advice.
>
> Cheers,
> Al.
>

[Domenic]

Try the following...

=SUMPRODUCT(--(C4:C34="A"),--(D4:D34="E"))

Hope this helps!

In article <[email protected]>,
"[email protected]" <[email protected]> wrote:

> Evening All,
>
> I am a novice Excel user, so please excuse the probably very basic
> question. I have a spreadsheet with two columns, e.g.
>
> A E
> D E
> F L
> A E
> G E
>
> I am trying to get some totals for column 2 based on the value, so I
> used COUNTIF(D4:D34, "E"). This is great, but I only want to count
> column 2 if the corresponding cell in column 1 is "A" (or not "A" for
> my other total). I think I want to somehow use INDIRECT as well, but
> can't quite figure out how! The total I am looking for in the example
> would be 2 (col2="E" and col1="A").
>
> Thanks for any hints/advice.
>
> Cheers,
> Al.

[[email protected]]

Thanks everyone for all your help there - superb! Just one question,
what does '--(C4:C34="A")' do - is it some method of creating an array
from the cells?

Thanks again,
Al.

[Bob Phillips]

See http://www.xldynamic.com/source/xld.SUMPRODUCT.html for an explanation

--

HTH

RP
(remove nothere from the email address if mailing direct)


<[email protected]> wrote in message
news:[email protected]...
> Thanks everyone for all your help there - superb! Just one question,
> what does '--(C4:C34="A")' do - is it some method of creating an array
> from the cells?
>
> Thanks again,
> Al.
>