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!

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

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!

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<""))

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!!