Sumif similar to Countif??

[Steve]

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!

 

[Ratheesh]

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 blankcells 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
(e-mail address removed)

 

[Jim Cone]

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" <[email protected]>
wrote in message
news:11421607.960.1331740136160.JavaMail.geo-discussion-forums@vbut24...

 

[Steve]

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