Simple Sumif formual with Criteria
 

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

Simple Sumif formual with Criteria
 

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

"Geo" wrote:

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

Simple Sumif formual with Criteria
 

On Feb 20, 9:22*pm, Geo wrote:
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

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.

Simple Sumif formual with Criteria
 

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


--
Biff
Microsoft Excel MVP


"Geo" wrote in message
...
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

Simple Sumif formual with Criteria
 

On Feb 20, 9:54*pm, yshridhar
wrote:
If the data is inthe range a1:a10
=countif(a1:a10,a1)
with regards
Sreedhar

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

Simple Sumif formual with Criteria
 

On Feb 20, 9:54*pm, yshridhar
wrote:
If the data is inthe range a1:a10
=countif(a1:a10,a1)
with regards
Sreedhar

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

Simple Sumif formual with Criteria
 

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

Try it like this:

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


--
Biff
Microsoft Excel MVP


"GTVT06" wrote in message
...
On Feb 20, 9:54 pm, yshridhar
wrote:
If the data is inthe range a1:a10
=countif(a1:a10,a1)
with regards
Sreedhar

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