Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Sumif similar to Countif??
Hello. I'm looking for a variation of the formula Countif tht will operate in a similar fashion to the Sumif.
In column A I have various categories. In column B I have either blank cells or numbers. I need to scan column A to indentify a category (Apple for example), and then COUNT the non-blank cells for Apple in column B. Any ideas? Thanks so much! |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Sumif similar to Countif??
On Mar 14, 8:48*pm, Steve wrote:
Hello. *I'm looking for a variation of the formula Countif tht will operate in a similar fashion to the Sumif. In column A I have various categories. *In column B I have either blank cells or numbers. *I need to scan column A to indentify a category (Apple for example), and then COUNT the non-blank cells for Apple in column B. Any ideas? *Thanks so much! Hi Steve, I hope that I understood your issue. If so, here you can use the excel Array inbuilt function to find out the count of "Apple" how many where B Column is Blank :-) e.g. You have some data from A1:A10, out of that 6 are "Apple" You have some data from B1:B10, out of that 3 are "Blank" and 3 are "Non-blank" So, here your answer should be 3 use the below formula in C1 =SUM(((A1:A10)="Apple")*((B1:B10)="")) Important: If you simply enter the above formula, you will not get the correct answer. So, after copying the above formula, select C1 and make it in editable mode (Press F2) Then do Ctrl + Shift + Enter You will get your answer "3" and the formula will change as {=SUM(((A1:A10)="Apple")*((B1:B10)=""))} This is array function. Please feel free to revert incase any doubt. Thank you. Ratheesh |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Sumif similar to Countif??
Hello again,
Set up an equation for each column that returns True or False for each cell. Convert the Boolean to a number using two minus signs (True to -1, then -1 to 1) Multiply the two columns together using Sumproduct... =SUMPRODUCT(--(A4:A22="Apple"),--(LEN(B4:B22)0)) -- Jim Cone Portland, Oregon USA . http://www.mediafire.com/PrimitiveSoftware . (Data Options add-in: Color, Delete or Insert specific rows/dates/random data) "Steve" wrote in message news:11421607.960.1331740136160.JavaMail.geo-discussion-forums@vbut24... Hello. I'm looking for a variation of the formula Countif tht will operate in a similar fashion to the Sumif. In column A I have various categories. In column B I have either blank cells or numbers. I need to scan column A to indentify a category (Apple for example), and then COUNT the non-blank cells for Apple in column B. Any ideas? Thanks so much! |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Sumif similar to Countif??
On Wed, 14 Mar 2012 08:48:56 -0700 (PDT), Steve wrote:
Hello. I'm looking for a variation of the formula Countif tht will operate in a similar fashion to the Sumif. In column A I have various categories. In column B I have either blank cells or numbers. I need to scan column A to indentify a category (Apple for example), and then COUNT the non-blank cells for Apple in column B. Any ideas? Thanks so much! If you have Excel 2007 or later, you can use COUNTIFS: =COUNTIFS(A:A,"apple",B:B,"<"&"") If you have an earlier version of Excel, use: =SUMPRODUCT((A1:A1000="apple")*(B1:B1000<"")) |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Sumif similar to Countif??
On Wednesday, March 14, 2012 9:48:56 AM UTC-6, Steve wrote:
Hello. I'm looking for a variation of the formula Countif tht will operate in a similar fashion to the Sumif. In column A I have various categories. In column B I have either blank cells or numbers. I need to scan column A to indentify a category (Apple for example), and then COUNT the non-blank cells for Apple in column B. Any ideas? Thanks so much! Thanks guys!! |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Max/min functions similar to SUMIF | Excel Discussion (Misc queries) | |||
Can I do a MAXIF similar to SUMIF and/or COUNTIF? Please Advise | Excel Discussion (Misc queries) | |||
similar to sumif() but for averages | Excel Discussion (Misc queries) | |||
A formula maxif, similar with sumif or countif | Excel Worksheet Functions | |||
sumif with similar cells | Excel Worksheet Functions |