Excel nested formular

[337alant]

are there any Excel formula experts out there
I am wanting to resolve multiple arguments in a spread sheet.

this is an example of what I am trying to do

If range A1 to A100 ="1" and (C1 to C100 = "open") count as 1
I only want to count the "open" if its related to a "1"

I have a spread sheet with random 1, 2, 3s in column A and open & Closed in column C. I am collecting statistics on the 1,2,3 that are open and closed.
I can easy get one of the functions to work with Countif but not when I have multiple criteria? can anyone help

Alan

 

[sam_cat]

And a formula to column D1 and drag it down.

If(and(a1=1,c1="open" ),1,0)

So if a1 is 1 and c1 is open then d1 will be a 1 else 0

Unless I am misunderstanding...

 

[demotivated]

This one stops a running total of repayments in column B from showing a minus balance in another column eg it stops at zero. Obviously there is a B2..B3..B4 etc.


=IF(45000-B1>-1,45000-B2,0)

 

[DevillEars]

Here's another approach:

Add the following formula in D1 and replicate down to all valid cells:

=IF(A1&C1="1open",1,0)

The "&" concatenates the values in A1 (1,2,3...) and C1 ("open" or "closed")
The IF compares the concatenated string against the target of "1open" and returns a "1" for a match otherwise returns a "0".

PS: On older versions of Excel you may need to convert A1 from a numeric value to a string and then the above formula would need to be:

=IF(FIXED(A1,0,TRUE)&C1="1open",1,0)

Where the function FIXED converts the value in A1 to a string with 0 decimal places and where the "TRUE" prevents commas between 1000s.

If you need more convoluted stats for various permutations of 1,2,3... and "open"/"closed", then the following approach may help:

Step 1 - Create a LOOKUP table with four columns (eg P, Q, R and S)

where P = Target String (formula: =Q1&R1
and Q = 1,2,3...
and R = "open"/"closed"
and S = returned value for any match

Sample Table:

P Q R S
1open 1 open 1
1closed 1 closed 0
2open 2 open 3
2closed 2 closed 0
3open 3 open 5
3closed 3 closed 0
etc....

Name the table area (eg P1:S6) as "LkUpTable"

In original D1 add a formula:

=VLOOKUP(A1&C1,LkUpTable,4,FALSE)

This formula will:

a) Construct a concatenated string from A1 and C1 (eg "1open")
b) Use the string to find an exact match (thanks to the "FALSE") in "LkUpTable"
c) Return the value from the 4th column in the matched row (S1)

You can use Column S to structure your stats retrieval values as require

I hope that helps...

Dave

 

[337alant]

Thanks guys Ill have a play with your examples and report back.
Our works computers still use Office 2003 BTW.
Alan

 

[EIffel]

You may also want to look into pivot tables, which would give you a count of open 1s, 2s, 3s, etc

 

[337alant]

And a formula to column D1 and drag it down.

If(and(a1=1,c1="open" ),1,0)

So if a1 is 1 and c1 is open then d1 will be a 1 else 0

Unless I am misunderstanding...

Sam your formula works on a single line and I can copy it down the sheet and then sum the 1s but can I build the counting into the formula so the overall result can be seen in one cell ?

=IF(AND(A1:A18=1,??and??C1:C18="open" ),1,0)

What I am trying to do is only count the cells with open in a range c1:c18 if within A1:a18 the cells contain a 1

So in the range A1:A18 I have 10 cells with "1", 4 cells with "2" and 4 cells with "3"
In the range C1:C18 I have 9 open and 9 closed

So in range A1:A18 so the first result is 10 but I only count the 1s if within the range C1:C18 the cell has Open in it so if I only have 9 with open the maximum result can only be 9 but the open has to coincide with a line with a 1 so it could be less

Its difficult to explain this stuff isnt it?.

Alan

 

[DevillEars]

Sam your formula works on a single line and I can copy it down the sheet and then sum the 1s but can I build the counting into the formula so the overall result can be seen in one cell ?
Alan

The simplest approach to obtaining a single-cell summary count of rows containing both "1" and "open" is to use a pivot table.

To simplify the process, I'd still add a column (D?) and populate it with the formula:

=A1&C1

Insert a row above the data (Row 1?) and unique headings for each column.
Use "String" for Column D
Set the row to Bold typeface.

Select the entire data range (A1101?) and:

a) Select "Insert" Tab
b) Select "Table"
c) Click on OK (range should be auto-filled and "table has headers" ticked)

This will create an Excel Data Table and create auto-filters for each column

Click on any cell in the Data Table and:

a) Select "Insert" Tab
b) Select "Pivot Table"
c) Click on OK (default will create pivot report in a new worksheet)

Create pivot report and add a filter to "String" field and select only "1open"
Set field attributes for 1,2,3 field to show "Count"

Voila!

Dave

 

[337alant]

Dave, Thanks I have done everything in your last mail but Im stuck on the last line with the

Set field attributes for 1,2,3 field to show "Count"

Im using my personal lap top at the moment with Excel 2010 on it

Alan

 

[DevillEars]

Hi Alan,

When you click on any field in a pivot table, you should be presented with the 4-block field menu:

Top left: Report Filters
Top Right: Column Labels
Bottom Left: Row Labels
Bottom Right: Value Fields

If you click on either the field in the Bottom Right block or on the report heading for the same field, the pop-up menu includes an option for Value Field Settings. Click on this and you will be presented with another pop-up allowing you to select "Summarise Values By" from a list including Sum, Count, Average, etc.

Select the option "Count" and click OK

The Pivot report will now provide a count of those rows that meet the filter criteria (i.e. = "1open")

I hope that helps

Dave

PS: Excel 2010 Pivot Tables are not fully compatible with Excel 2003, so be careful when trying to move from laptop to work system - you would be best advised to re-build on work PC using Excel 2003 just to be on the safe side.