HI All,

Thanks in advance for any help that can be provided on the question
below:

I need a function that will produce the total number of of a specific
device in Column A that corresponds with a specific value in column
B.

Personally, thinking it may be nicer if you have the requirements in a
separate table. So, assuming your list is in columns a and B, let's say your
required HDD is in D2 and required Status is in E2. In F2:

=SUMPRODUCT(--($A$2:$A$1000=D2),--($B$2:$B$1000=E2))

"MammaFin" wrote:

HI All,

Thanks in advance for any help that can be provided on the question
below:

I need a function that will produce the total number of of a specific
device in Column A that corresponds with a specific value in column
B.

E.G below column A represents a Hard Disk device of 133GB and Column B
represents if it is being used (Enabled) or not used (Unbound) - I
need to get the total number of 133GB devices that are in the
"Unbound" state.


HDD Status
133.6796875 Enabled
133.6796875 Enabled
133.6796875 Enabled
458.5957031 Enabled
458.5957031 Enabled
133.6796875 Enabled
133.6796875 Unbound
133.6796875 Unbound
133.6796875 Enabled
458.5957031 Unbound

Kind Regards,
Fin

Try this:

=SUMPRODUCT((A1:A20=133.6796875)*(B1:B20="Unbound" ))

I've assumed you have 20 rows of data, so adjust the ranges if you
have more.

If you are likely to want other summary stats, then you can put the
variables in two cells, eg:

C1=133.6796875
D1=Unbound

then the formula becomes:

=SUMPRODUCT((A1:A20=C1)*(B1:B20=D1))

Hope this helps.

Pete

On Sep 2, 5:35*pm, MammaFin wrote:
HI All,

Thanks in advance for any help that can be provided on the question
below:

I need a function that will produce the total number of of a specific
device *in Column A that corresponds with a specific value in column
B.

E.G below column A represents a Hard Disk device of 133GB and Column B
represents if it is being used (Enabled) or not used (Unbound) - I
need to get the total number of 133GB devices that are in the
"Unbound" state.

HDD * * * * * * * * * * Status
133.6796875 * * Enabled
133.6796875 * * Enabled
133.6796875 * * Enabled
458.5957031 * * Enabled
458.5957031 * * Enabled
133.6796875 * * Enabled
133.6796875 * * Unbound
133.6796875 * * Unbound
133.6796875 * * Enabled
458.5957031 * * Unbound

Kind Regards,
Fin

=SUMPRODUCT((INT(A2:A11)=133)*(B2:B11="Unbound"))



"MammaFin" wrote:

HI All,

Thanks in advance for any help that can be provided on the question
below:

I need a function that will produce the total number of of a specific
device in Column A that corresponds with a specific value in column
B.

E.G below column A represents a Hard Disk device of 133GB and Column B
represents if it is being used (Enabled) or not used (Unbound) - I
need to get the total number of 133GB devices that are in the
"Unbound" state.


HDD Status
133.6796875 Enabled
133.6796875 Enabled
133.6796875 Enabled
458.5957031 Enabled
458.5957031 Enabled
133.6796875 Enabled
133.6796875 Unbound
133.6796875 Unbound
133.6796875 Enabled
458.5957031 Unbound

Kind Regards,
Fin

If I understand correctly, you wanted to count the devices having number
€œ133.6796875€� with the status €œUnbound€�. If this is correct then you need
SUMIFS formula. Plz beware, this would only work with Excel 2007.

=COUNTIFS(A1:A10,"133.6796875", B1:B10,"Unbound")

This will give you the Total Number of devices having number €œ133.6796875€�
with the status €œUnbound€�.

--
Sheikh Saadi


"MammaFin" wrote:

HI All,

Thanks in advance for any help that can be provided on the question
below:

I need a function that will produce the total number of of a specific
device in Column A that corresponds with a specific value in column
B.

E.G below column A represents a Hard Disk device of 133GB and Column B
represents if it is being used (Enabled) or not used (Unbound) - I
need to get the total number of 133GB devices that are in the
"Unbound" state.


HDD Status
133.6796875 Enabled
133.6796875 Enabled
133.6796875 Enabled
458.5957031 Enabled
458.5957031 Enabled
133.6796875 Enabled
133.6796875 Unbound
133.6796875 Unbound
133.6796875 Enabled
458.5957031 Unbound

Kind Regards,
Fin