Simple Sumif formual with Criteria

G

Geo

Hello,
I'm a Novice at excel

I need a formula that can give me a total for a specific item when the
criteria is blank . There are multiple sheets and I would need it to update
automically as I add more sheets and Xs to the corresponding item. please see
ex. below

Column A Column B
Waiting For Model: Phone Ready
Muziq X
Fusic
Muziq
Fusic X
muziq
Moto Q
Fusic
Moto Q
Fusic
....... .....


So what I'm looking for is
Waiting For Model: Total
Muziq 2
Moto Q 2
Fusic 3


Thanks in advance
 
G

GTVT06

Hello,
I'm a Novice at excel

I need a formula that can give me a total for a specific item when the
criteria is blank . There are multiple sheets and I would need it to update
automically as I add more sheets and Xs to the corresponding item. please see
ex. below

Column A                    Column B
Waiting For Model:     Phone Ready    
Muziq                              X
Fusic
Muziq
Fusic                               X
muziq
Moto Q
Fusic                          
Moto Q
Fusic                          
......                               .....

So what I'm looking for is  
Waiting For Model:      Total
Muziq                             2
Moto Q                           2
Fusic                              3

Thanks in advance
Click to expand...

You and use either of these formulas:
=SUMPRODUCT(--(A1:A9="Muziq")*(B1:B9="")*1)
or
=SUM(IF((A1:A9="Muziq")*(B1:B9=""),(A1:A9="Muziq")*(B1:B9="")*1))
entered as an array formula with Ctrl+Shift+Enter

They both give the same result.
 
T

T. Valko

Try this:

E2:E4 = Muziq, Moto Q, Fusic

Enter this formula in F2 and copy down to F4:

=SUMPRODUCT(--(A$2:A$10=E2),--(B$2:B$10=""))
 
G

GTVT06

If the data is inthe range a1:a10
=countif(a1:a10,a1)
with regards
Sreedhar
Click to expand...

yshridhar,
That'll give him the total count for that product but he don't want to
count all of the products, he only wants to count the product if
column B is blank
 
G

GTVT06

If the data is inthe range a1:a10
=countif(a1:a10,a1)
with regards
Sreedhar
Click to expand...

He could however, use a COUNT(IF similar to my SUM(IF suggestion by
entering:
=COUNT(IF((A1:A9="Muziq")*(B1:B9=""),(A1:A9="Muziq")*(B1:B9="")*1))
*Entered as an array formula with Ctrl+Shift+Enter

As with pretty much anything in Excel, There are several ways to get
the results he's looking for
 
T

T. Valko

=COUNT(IF((A1:A9="Muziq")*(B1:B9=""),(A1:A9="Muziq")*(B1:B9="")*1))
*Entered as an array formula with Ctrl+Shift+Enter
Click to expand...

Try it like this:

=COUNT(IF((A1:A9="Muziq")*(B1:B9=""),1))
=COUNT(1/((A1:A9="Muziq")*(B1:B9="")))


--
Biff
Microsoft Excel MVP


If the data is inthe range a1:a10
=countif(a1:a10,a1)
with regards
Sreedhar
Click to expand...

He could however, use a COUNT(IF similar to my SUM(IF suggestion by
entering:
=COUNT(IF((A1:A9="Muziq")*(B1:B9=""),(A1:A9="Muziq")*(B1:B9="")*1))
*Entered as an array formula with Ctrl+Shift+Enter

As with pretty much anything in Excel, There are several ways to get
the results he's looking for
 
Top