Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 529
  • Last Modified:

Excel operating on every other column

I have these data sheets we import and must maintain the layout in which they come in. The issue is that the columns are sequenced so that we need to work with every-other column.

For example, the columns are labeled NEAR and then FAR alternatively as so:                                    


      NEAR FAR NEAR FAR NEAR FAR

I am trying to count the number of results that are less than 0 in either the NEAR Columns or the FAR columns.

I have tried to create a named range for the "NEAR" (and "FAR ") values but the formula will not accept the named range.
                                                                  
                                                             
=COUNTIF(NEAR,"<0")      #VALUE!                                                                  
=COUNTIF(FAR ,"<0")      #VALUE!                                                                  
                                                                        
Named Ranges            
                                                            
NEAR=Sheet1!$B$2,Sheet1!$D$2, Sheet1!$F$2,Sheet1!$H$2, Sheet1!$J$2,Sheet1!$L$2                                    
FAR = Sheet1!$C$2,Sheet1!$E$2, Sheet1!$G$2,Sheet1!$I$2, Sheet1!$K$2,Sheet1!$M$2


Is there a way to count the values for just the NEAR data (or the FAR data) that is less than 0?
0
Zipbang
Asked:
Zipbang
  • 3
  • 3
  • 2
  • +2
1 Solution
 
leptonkaCommented:
Hi,

Name your NEAR-FAR header as "header" and name the data part below this header as "data" so you can use this formula to count data below 0:
=SUMPRODUCT(--((header="NEAR")*data<0))
=SUMPRODUCT(--((header="FAR")*data<0))

Hope it works.
Cheers,
Kris
0
 
barry houdiniCommented:
If you have Excel 2007 or later you can also do that with COUNTIFS

=COUNTIFS(header,"near",data,"<0")

regards, barry
0
 
leptonkaCommented:
barry, I assumed "data" contains more than one row.
0
VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

 
NBVCCommented:
Here is a way using the Named Range for non-contiguous cells..


=SUMPRODUCT((LARGE(Near,ROW(INDIRECT("1:"&COUNT(Near))))>0)*(LARGE(Near,ROW(INDIRECT("1:"&COUNT(Near))))))
0
 
SteveCommented:
The following entered as array formula (with [ctrl]+[shift]+[enter]) will count up odd columns between A and L above 0:
=SUM((A2:L2>0)*(MOD(COLUMN(A2:L2),2)))

This will do even columns
=SUM((A2:L2>0)*(MOD(COLUMN(A2:L2)+1,2)))
0
 
barry houdiniCommented:
>barry, I assumed "data" contains more than one row.

Hello Kris

Yes, you are correct - my suggestion won't work if the data is multiple rows.....but in the question Near and Far are all cells within row 2. Is that just an example, Zipbang, or is that the extent of your data?

>=SUMPRODUCT((LARGE(Near,ROW(INDIRECT("1:"&COUNT(Near))))>0)*(LARGE(Near,ROW(INDIRECT("1:"&COUNT(Near))))))

Hi NBVC,

Isn't that giving a sum of the positive cells?

You can get a count of values < 0 using that approach with

=SUMPRODUCT(((LARGE(Near,ROW(INDIRECT("1:"&COUNT(Near))))<0)+0))

or perhaps

=INDEX(FREQUENCY(Near,-0.00000000000001),1)
0
 
ZipbangAuthor Commented:
The data is in multiple rows (varies in how many) and multiple columns (again, varies as to how many)

thank you,

Chris
0
 
SteveCommented:
The formulas below entered with [ctrl]+[shift]+[enter]:
 
=SUM((A2:Z20>0)*(MOD(COLUMN(A2:Z20),2)))
will count all cells in the odd columns (ACEGI...) greater than 0 in the range AtoZ rows 2to20.

=SUM((A2:Z20>0)*(MOD(COLUMN(A2:Z20)+1,2)))
will count all cells in the odd columns (BDFHJ...) greater than 0 in the range AtoZ rows 2to20.

The formula can be changed to suit the dize of the data, but not to include whole columns or rows
0
 
leptonkaCommented:
Using the formula by The_Barman with SUMPRODUCT you will not need [ctrl]+[shift]+[enter].

Or if you would like to summarize by the text in your header (A1:Z1), you can do it as I mentioned above:
=SUMPRODUCT(--((A1:Z1="NEAR")*A2:Z20<0))
=SUMPRODUCT(--((A1:Z1="FAR")*A2:Z20<0))

Cheers,
Kris
0
 
NBVCCommented:
Hi Barry,

You are correct, I carried it too far and summed the positive instead of count the negatives....

thanks for catching that.
0
 
ZipbangAuthor Commented:
Thanks to all for very good responses.  I must go with The_Barman as that is what I ended up using and it works perfectly.

Thanks again.

TCC
zipbang
0
 
NBVCCommented:
FYI,

The reason why Barry and I used the formulas we did, is that we (at least I) thought that the Named Range was important for you.  With the Named Range and our formulas, the data does not necessarily have to be distributed in equidistant columns.....
0

Featured Post

What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

  • 3
  • 3
  • 2
  • +2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now