Count if with several areas/conditions
 

I want to create a countif formula containing two areas and conditions, i.e.
=countif(A1:A30;"V1") AND (F1:F30;"A"). How do I write the formula to make
it work? Please help...

Count if with several areas/conditions
 

Hi Marielle

One way
=SUMPRODUCT(--(A1:A30="V"),--(F1:F30="A"))

--
Regards

Roger Govier


"Marielle" wrote in message
...
I want to create a countif formula containing two areas and conditions,
i.e.
=countif(A1:A30;"V1") AND (F1:F30;"A"). How do I write the formula to
make
it work? Please help...

Count if with several areas/conditions
 

Hi and thanks!

Unfortunately I still don't get the result I want... I must still be doing
something wrong... Or are there other ways to do it? I'll try to explain a
bit mo

Here's an example of the table I use:

Window type Floor Size Etc.
V3.1 1
V3.1 1
V3.1 2
V3.1 3
V3.1 3
V3.1 3
V3.2 1
V3.2 1
V3.2 2

I want the formula to count how many of each window type there are in each
floor. In this case the result would be:

Window type Floor 1 Floor 2 Floor 3
V3.1 2 1 3
V3.2 2 1 0

I hope you can help me. I've been stuck with this problem for two days!
Thank you ;o)

Marielle


"Roger Govier" wrote:

Hi Marielle

One way
=SUMPRODUCT(--(A1:A30="V"),--(F1:F30="A"))

--
Regards

Roger Govier


"Marielle" wrote in message
...
I want to create a countif formula containing two areas and conditions,
i.e.
=countif(A1:A30;"V1") AND (F1:F30;"A"). How do I write the formula to
make
it work? Please help...

Count if with several areas/conditions
 

Hi and thanks!

Unfortunately I still don't get the result I want. I must be doing something
wrong... Or are there other ways to do this? Let me try to explain my problem
a bit mo

Ihave a table that looks like this:

Window type Building Floor Size Etc...
V3.1 A
V3.1 A
V3.1 B
V3.1 B
V3.1 B
V3.1 C
V3.2 A
V3.2 B
V3.2 B
V3.2 C
V3.2 C
V3.2 C

I would like the formula to count how many windows there are of each window
type in each building, and the result should be like this:

Window type Buldining A Building B Building C
V3.1 2 3
1
V3.2 1 2
3

How do I make this happen? I've been stuck with this problem for 2 days!
Very grateful for all help ;o)


Marielle

"Roger Govier" wrote:

Hi Marielle

One way
=SUMPRODUCT(--(A1:A30="V"),--(F1:F30="A"))

--
Regards

Roger Govier


"Marielle" wrote in message
...
I want to create a countif formula containing two areas and conditions,
i.e.
=countif(A1:A30;"V1") AND (F1:F30;"A"). How do I write the formula to
make
it work? Please help...

Count if with several areas/conditions
 

Hi Marielle

You have changed the criteria a little
Assuming your data table is located in A1:B10 with headers in row 1
Assuming your report table has Window in H1, Floor 1 in I1, Floor 2 in
J1, Floor 3 in K1 and has V3.1 in H2 and V3.2 in H3

enter is cell I2
=SUMPRODUCT(--($A$2:$A$10=$H2),--($B$2:$B$10=--RIGHT(I$1)))
copy across through cells J2:K2
copy I2:K2 to I3:K3

--
Regards

Roger Govier


"Marielle" wrote in message
...
Hi and thanks!

Unfortunately I still don't get the result I want... I must still be
doing
something wrong... Or are there other ways to do it? I'll try to
explain a
bit mo

Here's an example of the table I use:

Window type Floor Size Etc.
V3.1 1
V3.1 1
V3.1 2
V3.1 3
V3.1 3
V3.1 3
V3.2 1
V3.2 1
V3.2 2

I want the formula to count how many of each window type there are in
each
floor. In this case the result would be:

Window type Floor 1 Floor 2 Floor 3
V3.1 2 1 3
V3.2 2 1 0

I hope you can help me. I've been stuck with this problem for two
days!
Thank you ;o)

Marielle


"Roger Govier" wrote:

Hi Marielle

One way
=SUMPRODUCT(--(A1:A30="V"),--(F1:F30="A"))

--
Regards

Roger Govier


"Marielle" wrote in message
...
I want to create a countif formula containing two areas and
conditions,
i.e.
=countif(A1:A30;"V1") AND (F1:F30;"A"). How do I write the formula
to
make
it work? Please help...

Count if with several areas/conditions
 

Assume you tables is in A1:B50 with row 1 having

Window Type Floor


and is located on sheet1

Assume on sheet2 you have the results table you show with the upper left
corner in A1

in B2 you would put a formula like

=Sumproduct(--(Sheet1!$A$:$A$50=$A2),--(Sheet1!$B$1:$B$50=Right(B$1,1)*1))

then drag fill this formula down and to the right.

--
Regards,
Tom Ogilvy

"Marielle" wrote in message
...
Hi and thanks!

Unfortunately I still don't get the result I want... I must still be doing
something wrong... Or are there other ways to do it? I'll try to explain a
bit mo

Here's an example of the table I use:

Window type Floor Size Etc.
V3.1 1
V3.1 1
V3.1 2
V3.1 3
V3.1 3
V3.1 3
V3.2 1
V3.2 1
V3.2 2

I want the formula to count how many of each window type there are in each
floor. In this case the result would be:

Window type Floor 1 Floor 2 Floor 3
V3.1 2 1 3
V3.2 2 1 0

I hope you can help me. I've been stuck with this problem for two days!
Thank you ;o)

Marielle


"Roger Govier" wrote:

Hi Marielle

One way
=SUMPRODUCT(--(A1:A30="V"),--(F1:F30="A"))

--
Regards

Roger Govier


"Marielle" wrote in message
...
I want to create a countif formula containing two areas and conditions,
i.e.
=countif(A1:A30;"V1") AND (F1:F30;"A"). How do I write the formula to
make
it work? Please help...