Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Count cells based on whether another cell is blank or not
I have a spreadsheet with data in 2 columns and I want to be able to
figure out how many times there is data in col A and Col B of each row. If either cell is empty I don't want it counted. Basically I want to count the amount of times that there is data in col a and col b along the same row. Thanks. |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Count cells based on whether another cell is blank or not
=SUMPRODUCT(--(A2:A500<""),--(B2:B500<""))
will do this, note that unless you have Excel 2007 you can't use the whole column -- Regards, Peo Sjoblom "luvnrocs" wrote in message ... I have a spreadsheet with data in 2 columns and I want to be able to figure out how many times there is data in col A and Col B of each row. If either cell is empty I don't want it counted. Basically I want to count the amount of times that there is data in col a and col b along the same row. Thanks. |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Count cells based on whether another cell is blank or not
One way
Something like this, in say C1: =SUMPRODUCT(--((A1:A10<"")+(B1:B10<"")0)) -- Max Singapore http://savefile.com/projects/236895 Downloads:15,900 Files:354 Subscribers:53 xdemechanik --- "luvnrocs" wrote in message ... I have a spreadsheet with data in 2 columns and I want to be able to figure out how many times there is data in col A and Col B of each row. If either cell is empty I don't want it counted. Basically I want to count the amount of times that there is data in col a and col b along the same row. Thanks. |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Count cells based on whether another cell is blank or not
That would be either A or B not A and B
To me it sounds as if the OP wants A and B "Basically I want to count the amount of times that there is data in col a and col b along the same row" ^^^^^^^^^^^^^ -- Regards, Peo Sjoblom "Max" wrote in message ... One way Something like this, in say C1: =SUMPRODUCT(--((A1:A10<"")+(B1:B10<"")0)) -- Max Singapore http://savefile.com/projects/236895 Downloads:15,900 Files:354 Subscribers:53 xdemechanik --- "luvnrocs" wrote in message ... I have a spreadsheet with data in 2 columns and I want to be able to figure out how many times there is data in col A and Col B of each row. If either cell is empty I don't want it counted. Basically I want to count the amount of times that there is data in col a and col b along the same row. Thanks. |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Count cells based on whether another cell is blank or not
Agreed, my suggestion was focusing on this OP's line:
.. If either cell is empty I don't want it counted. To me, it sounds from the line that OP wants either A or B -- Max Singapore http://savefile.com/projects/236895 Downloads:15,900 Files:354 Subscribers:53 xdemechanik --- "Peo Sjoblom" wrote in message ... That would be either A or B not A and B To me it sounds as if the OP wants A and B "Basically I want to count the amount of times that there is data in col a and col b along the same row" ^^^^^^^^^^^^^ |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Count cells based on whether another cell is blank or not
But adding them together the way you did will counted if either one is
true... I read (as did Peo) the OP as saying he does *not* want that.... either empty, *don't* count it. Rick "Max" wrote in message ... Agreed, my suggestion was focusing on this OP's line: .. If either cell is empty I don't want it counted. To me, it sounds from the line that OP wants either A or B -- Max Singapore http://savefile.com/projects/236895 Downloads:15,900 Files:354 Subscribers:53 xdemechanik --- "Peo Sjoblom" wrote in message ... That would be either A or B not A and B To me it sounds as if the OP wants A and B "Basically I want to count the amount of times that there is data in col a and col b along the same row" ^^^^^^^^^^^^^ |
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Count cells based on whether another cell is blank or not
Rick,
Shall we leave it to the OP to clarify what s/he really wants, since what's originally posted is kinda ambiguous? One of the 2 suggestions given should fit the bill, with the other held as a solution for the other scenario. P/s: Peo's response wasn't visible to me before I responded (our responses were only 6 minutes apart) -- Max Singapore http://savefile.com/projects/236895 Downloads: 15,900, Files: 354, Subscribers: 53 xdemechanik --- "Rick Rothstein (MVP - VB)" wrote in message ... But adding them together the way you did will counted if either one is true... I read (as did Peo) the OP as saying he does *not* want that.... either empty, *don't* count it. Rick |
#8
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Count cells based on whether another cell is blank or not
On Jul 16, 7:23 pm, "Max" wrote:
Agreed, my suggestion was focusing on this OP's line: .. If either cell is empty I don't want it counted. To me, it sounds from the line that OP wants either A or B -- Thank you both Peo and Max. I ended up using Peo's formula and it works great. Jenn Max Singaporehttp://savefile.com/projects/236895 Downloads:15,900 Files:354 Subscribers:53 xdemechanik ---"Peo Sjoblom" wrote in message ... That would be either A or B not A and B To me it sounds as if the OP wants A and B "Basically I want to count the amount of times that there is data in col a and col b along the same row" ^^^^^^^^^^^^^ |
#9
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Count cells based on whether another cell is blank or not
Thank you both Peo and Max. I ended up using Peo's formula and it
works great. Welcome. Thanks for feeding back here, and clarifying. You can hold my suggestion to apply for the other scenario, should it ever be encountered <g -- Max Singapore http://savefile.com/projects/236895 Downloads: 16,000, Files: 354, Subscribers: 53 xdemechanik --- |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Count with multiple criteria based on cell not blank? | Excel Worksheet Functions | |||
Count from Blank & Non-Blank Cells | Excel Discussion (Misc queries) | |||
Count the non blank cells | Excel Worksheet Functions | |||
Do Not count blank cells | Excel Discussion (Misc queries) | |||
COUNT ONLY CELLS THAT AREN'T BLANK | Excel Worksheet Functions |