Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.newusers
external usenet poster
 
Posts: 9
Default Countif non consecutive columns

I have the following:
Attended Attended
A B C D E F
1 John Doe x Y x N ____

Column B and D with the x are if they are to attend and column C and E are
whether they attended or not and it continues for several other courses.

How do I sum or countif the nonconsecutive columns are Y? It will be column
C, E, G, I etc but the same row. I need to tally how many they actually
attended vs not attned in another column next to it.

Thank you! Hope this makes sense!
  #2   Report Post  
Posted to microsoft.public.excel.newusers
external usenet poster
 
Posts: 846
Default Countif non consecutive columns

This is one way
=--(C2="Y")+--(E2="Y")+--(G2="Y")

added cells +--(I2="Y") as needed
--
Wag more, bark less


"shamor" wrote:

I have the following:
Attended Attended
A B C D E F
1 John Doe x Y x N ____

Column B and D with the x are if they are to attend and column C and E are
whether they attended or not and it continues for several other courses.

How do I sum or countif the nonconsecutive columns are Y? It will be column
C, E, G, I etc but the same row. I need to tally how many they actually
attended vs not attned in another column next to it.

Thank you! Hope this makes sense!

  #3   Report Post  
Posted to microsoft.public.excel.newusers
external usenet poster
 
Posts: 9
Default Countif non consecutive columns

This does not work it gives me a true statement and it counted a "N" as true.

What I need is for it to count or sum a total in number format of the "Y" in
the non-consecutive columns. That column could be a Y or N and I need to
count the Y in non-consecutive columns across a spreadsheet.

Thanks!

"Brad" wrote:

This is one way
=--(C2="Y")+--(E2="Y")+--(G2="Y")

added cells +--(I2="Y") as needed
--
Wag more, bark less


"shamor" wrote:

I have the following:
Attended Attended
A B C D E F
1 John Doe x Y x N ____

Column B and D with the x are if they are to attend and column C and E are
whether they attended or not and it continues for several other courses.

How do I sum or countif the nonconsecutive columns are Y? It will be column
C, E, G, I etc but the same row. I need to tally how many they actually
attended vs not attned in another column next to it.

Thank you! Hope this makes sense!

  #4   Report Post  
Posted to microsoft.public.excel.newusers
external usenet poster
 
Posts: 846
Default Countif non consecutive columns

I tested this on my computer before posting and it provides the correct number.

Only counting the "Y" as one....

Like to figure the problem out with you. Please check to make sure that you
are adding up the correct cells. (Tracing dependents can make sure that you
are not off a row or two...) Can you post the equation that you have entered?

--
Wag more, bark less


"shamor" wrote:

This does not work it gives me a true statement and it counted a "N" as true.

What I need is for it to count or sum a total in number format of the "Y" in
the non-consecutive columns. That column could be a Y or N and I need to
count the Y in non-consecutive columns across a spreadsheet.

Thanks!

"Brad" wrote:

This is one way
=--(C2="Y")+--(E2="Y")+--(G2="Y")

added cells +--(I2="Y") as needed
--
Wag more, bark less


"shamor" wrote:

I have the following:
Attended Attended
A B C D E F
1 John Doe x Y x N ____

Column B and D with the x are if they are to attend and column C and E are
whether they attended or not and it continues for several other courses.

How do I sum or countif the nonconsecutive columns are Y? It will be column
C, E, G, I etc but the same row. I need to tally how many they actually
attended vs not attned in another column next to it.

Thank you! Hope this makes sense!

  #5   Report Post  
Posted to microsoft.public.excel.newusers
external usenet poster
 
Posts: 3,268
Default Countif non consecutive columns

As long you are counting every other row you can use this

=SUMPRODUCT(--(MOD(COLUMN(C2:Z2),2)=1),--(C2:Z2="Y"))


--


Regards,


Peo Sjoblom


"shamor" wrote in message
...
This does not work it gives me a true statement and it counted a "N" as
true.

What I need is for it to count or sum a total in number format of the "Y"
in
the non-consecutive columns. That column could be a Y or N and I need to
count the Y in non-consecutive columns across a spreadsheet.

Thanks!

"Brad" wrote:

This is one way
=--(C2="Y")+--(E2="Y")+--(G2="Y")

added cells +--(I2="Y") as needed
--
Wag more, bark less


"shamor" wrote:

I have the following:
Attended Attended
A B C D E F
1 John Doe x Y x N ____

Column B and D with the x are if they are to attend and column C and E
are
whether they attended or not and it continues for several other
courses.

How do I sum or countif the nonconsecutive columns are Y? It will be
column
C, E, G, I etc but the same row. I need to tally how many they actually
attended vs not attned in another column next to it.

Thank you! Hope this makes sense!





  #6   Report Post  
Posted to microsoft.public.excel.newusers
external usenet poster
 
Posts: 9
Default Countif non consecutive columns

I have done the below 2 formulas and they didn't work:

=G2(G2="Y")+K2(K2="Y")+O2(O2="Y")
=--(G2="Y")+--(K2="Y")+--(O2="Y")

Now I did this one and it worked but I have to identify the entire row and I
didn't want to have to do that in case I have to add a column that requires a
"Y" answer but does not need to get counted in that end result.......

=COUNTIF(F2:AM2, "Y")

Thanks much!

"Brad" wrote:

I tested this on my computer before posting and it provides the correct number.

Only counting the "Y" as one....

Like to figure the problem out with you. Please check to make sure that you
are adding up the correct cells. (Tracing dependents can make sure that you
are not off a row or two...) Can you post the equation that you have entered?

--
Wag more, bark less


"shamor" wrote:

This does not work it gives me a true statement and it counted a "N" as true.

What I need is for it to count or sum a total in number format of the "Y" in
the non-consecutive columns. That column could be a Y or N and I need to
count the Y in non-consecutive columns across a spreadsheet.

Thanks!

"Brad" wrote:

This is one way
=--(C2="Y")+--(E2="Y")+--(G2="Y")

added cells +--(I2="Y") as needed
--
Wag more, bark less


"shamor" wrote:

I have the following:
Attended Attended
A B C D E F
1 John Doe x Y x N ____

Column B and D with the x are if they are to attend and column C and E are
whether they attended or not and it continues for several other courses.

How do I sum or countif the nonconsecutive columns are Y? It will be column
C, E, G, I etc but the same row. I need to tally how many they actually
attended vs not attned in another column next to it.

Thank you! Hope this makes sense!

  #7   Report Post  
Posted to microsoft.public.excel.newusers
external usenet poster
 
Posts: 846
Default Countif non consecutive columns

I'm currently working in Excel 2007

In the Formula dropdown there is an "Evaluate Formula" options (2003 has the
same function the was to access it might be slightly different) - when you
evaluate the formula where does it not give you what you expect...

By the way Peo's answer is a more elegant answer than mine but it
essentially does the same thing.....
--
Wag more, bark less


"shamor" wrote:

I have done the below 2 formulas and they didn't work:

=G2(G2="Y")+K2(K2="Y")+O2(O2="Y")
=--(G2="Y")+--(K2="Y")+--(O2="Y")

Now I did this one and it worked but I have to identify the entire row and I
didn't want to have to do that in case I have to add a column that requires a
"Y" answer but does not need to get counted in that end result.......

=COUNTIF(F2:AM2, "Y")

Thanks much!

"Brad" wrote:

I tested this on my computer before posting and it provides the correct number.

Only counting the "Y" as one....

Like to figure the problem out with you. Please check to make sure that you
are adding up the correct cells. (Tracing dependents can make sure that you
are not off a row or two...) Can you post the equation that you have entered?

--
Wag more, bark less


"shamor" wrote:

This does not work it gives me a true statement and it counted a "N" as true.

What I need is for it to count or sum a total in number format of the "Y" in
the non-consecutive columns. That column could be a Y or N and I need to
count the Y in non-consecutive columns across a spreadsheet.

Thanks!

"Brad" wrote:

This is one way
=--(C2="Y")+--(E2="Y")+--(G2="Y")

added cells +--(I2="Y") as needed
--
Wag more, bark less


"shamor" wrote:

I have the following:
Attended Attended
A B C D E F
1 John Doe x Y x N ____

Column B and D with the x are if they are to attend and column C and E are
whether they attended or not and it continues for several other courses.

How do I sum or countif the nonconsecutive columns are Y? It will be column
C, E, G, I etc but the same row. I need to tally how many they actually
attended vs not attned in another column next to it.

Thank you! Hope this makes sense!

  #8   Report Post  
Posted to microsoft.public.excel.newusers
external usenet poster
 
Posts: 846
Default Countif non consecutive columns

Shamor

I would like you to use the middle formula
=--(G2="Y")+--(K2="Y")+--(O2="Y")


--
Wag more, bark less


"shamor" wrote:

I have done the below 2 formulas and they didn't work:

=G2(G2="Y")+K2(K2="Y")+O2(O2="Y")
=--(G2="Y")+--(K2="Y")+--(O2="Y")

Now I did this one and it worked but I have to identify the entire row and I
didn't want to have to do that in case I have to add a column that requires a
"Y" answer but does not need to get counted in that end result.......

=COUNTIF(F2:AM2, "Y")

Thanks much!

"Brad" wrote:

I tested this on my computer before posting and it provides the correct number.

Only counting the "Y" as one....

Like to figure the problem out with you. Please check to make sure that you
are adding up the correct cells. (Tracing dependents can make sure that you
are not off a row or two...) Can you post the equation that you have entered?

--
Wag more, bark less


"shamor" wrote:

This does not work it gives me a true statement and it counted a "N" as true.

What I need is for it to count or sum a total in number format of the "Y" in
the non-consecutive columns. That column could be a Y or N and I need to
count the Y in non-consecutive columns across a spreadsheet.

Thanks!

"Brad" wrote:

This is one way
=--(C2="Y")+--(E2="Y")+--(G2="Y")

added cells +--(I2="Y") as needed
--
Wag more, bark less


"shamor" wrote:

I have the following:
Attended Attended
A B C D E F
1 John Doe x Y x N ____

Column B and D with the x are if they are to attend and column C and E are
whether they attended or not and it continues for several other courses.

How do I sum or countif the nonconsecutive columns are Y? It will be column
C, E, G, I etc but the same row. I need to tally how many they actually
attended vs not attned in another column next to it.

Thank you! Hope this makes sense!

  #9   Report Post  
Posted to microsoft.public.excel.newusers
external usenet poster
 
Posts: 10,593
Default Countif non consecutive columns

If you use a + operator, the -- is superfluous.

--
---
HTH

Bob


(there's no email, no snail mail, but somewhere should be gmail in my addy)



"Brad" wrote in message
...
Shamor

I would like you to use the middle formula
=--(G2="Y")+--(K2="Y")+--(O2="Y")


--
Wag more, bark less


"shamor" wrote:

I have done the below 2 formulas and they didn't work:

=G2(G2="Y")+K2(K2="Y")+O2(O2="Y")
=--(G2="Y")+--(K2="Y")+--(O2="Y")

Now I did this one and it worked but I have to identify the entire row
and I
didn't want to have to do that in case I have to add a column that
requires a
"Y" answer but does not need to get counted in that end result.......

=COUNTIF(F2:AM2, "Y")

Thanks much!

"Brad" wrote:

I tested this on my computer before posting and it provides the correct
number.

Only counting the "Y" as one....

Like to figure the problem out with you. Please check to make sure
that you
are adding up the correct cells. (Tracing dependents can make sure
that you
are not off a row or two...) Can you post the equation that you have
entered?

--
Wag more, bark less


"shamor" wrote:

This does not work it gives me a true statement and it counted a "N"
as true.

What I need is for it to count or sum a total in number format of the
"Y" in
the non-consecutive columns. That column could be a Y or N and I need
to
count the Y in non-consecutive columns across a spreadsheet.

Thanks!

"Brad" wrote:

This is one way
=--(C2="Y")+--(E2="Y")+--(G2="Y")

added cells +--(I2="Y") as needed
--
Wag more, bark less


"shamor" wrote:

I have the following:
Attended Attended
A B C D E
F
1 John Doe x Y x N
____

Column B and D with the x are if they are to attend and column C
and E are
whether they attended or not and it continues for several other
courses.

How do I sum or countif the nonconsecutive columns are Y? It will
be column
C, E, G, I etc but the same row. I need to tally how many they
actually
attended vs not attned in another column next to it.

Thank you! Hope this makes sense!



Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
Using Countif to Count Consecutive Relative Increases in Numbers SteveC Excel Discussion (Misc queries) 16 September 27th 06 04:55 PM
Countif in non-consecutive cells michaelberrier Excel Discussion (Misc queries) 4 June 24th 06 10:43 PM
Count non consecutive columns [email protected] Excel Worksheet Functions 7 May 23rd 06 03:24 PM
how do i add consecutive columns in excel ? debi Excel Discussion (Misc queries) 1 October 13th 05 06:11 PM
sumif/countif on non-consecutive colums - array? Gerald Koch Excel Worksheet Functions 2 November 29th 04 06:21 PM


All times are GMT +1. The time now is 11:41 AM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"