Home |
Search |
Today's Posts |
#1
|
|||
|
|||
Referencing previous column in COUNTIF
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. |
#2
|
|||
|
|||
=SUMPRODUCT(--(A1:A10="A"),--(B1:B10-"E"))
-- HTH RP (remove nothere from the email address if mailing direct) wrote in message oups.com... 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. |
#3
|
|||
|
|||
Try this:
=countif(D4:D34, "E") + countif(A4:A34, "A") " 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. |
#4
|
|||
|
|||
Try the following...
=SUMPRODUCT(--(C4:C34="A"),--(D4:D34="E")) Hope this helps! In article .com, " 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. |
#5
|
|||
|
|||
=SUMPRODUCT(--(A2:A100="A"),--(B2:B100="E"))
will count "E" in B where A is "A" -- Regards, Peo Sjoblom wrote in message oups.com... 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. |
#6
|
|||
|
|||
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. |
#7
|
|||
|
|||
See http://www.xldynamic.com/source/xld.SUMPRODUCT.html for an explanation
-- HTH RP (remove nothere from the email address if mailing direct) wrote in message oups.com... 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. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
How do I sort by row instead of by column? | Excel Worksheet Functions | |||
Data in column predetermined by what has been entered in previous | Excel Discussion (Misc queries) | |||
How do you use countif for several column criteria? | Excel Worksheet Functions | |||
Countif (same as previous) | Excel Worksheet Functions | |||
Countif - Countif | Excel Worksheet Functions |