Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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... |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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... |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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... |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
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... |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
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... |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
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... |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Count with conditions? | Excel Discussion (Misc queries) | |||
Get the row count of different areas in the Excel | Excel Discussion (Misc queries) | |||
Count if - 2 conditions | Excel Discussion (Misc queries) | |||
How to count after 2 conditions are met | Excel Worksheet Functions | |||
Count ifs - 2 conditions | Excel Discussion (Misc queries) |