Help with Nested Formula

  • Thread starter Thread starter Allencp
  • Start date Start date
A

Allencp

I am trying to create a function to count the number of cells in a range
where 2 conditions must be met before the cells will be counted. if
"a1:a5=apples and b1:b5 =texas, then return with number of matches (for
example).

A1 B1 c1
apples texas spring
grapes florida winter
oranges texas summer
apples virgina summer
peach georgia fall
fruit virginia summer

For example, I want to know how many "apples" come "texas".

Assuming the above is possible, is it also possible to do the above
using the "SUBTOTAL" function, because I use filters frequently? For
example, to filter for spring AND apples from texas.
 
One way via SUMPRODUCT

Assuming your sample dataset is in A1:C6

Put in say, D1: =SUMPRODUCT((A1:A6="apples")*(B1:B6="texas"))

Note that the ranges have to be identical (i.e.: A1:A6, B1:B6)
and you can't use entire columns (e.g.: A:A, B:B) in SUMPRODUCT
 
Thanks for the help. It worked great!!!!!!!!

Any tips on how to make it work with SUBTOTAL, so I can use the sam
principal with filters
 
IMO, it'd be much easier to use a pivot table [PT]
to provide the summary info all at one go.

Perhaps some steps* to ease you-in?
*steps in xl97

Assume you have a data-set in Sheet1, in A1:C6
with row1 containing the headers:

Fruit State Season
apples texas spring
grapes florida winter
oranges texas summer
apples virginia summer
peach georgia fall

Click on any cell inside the data-set

Click > Data > PivotTable Report > Next
The range "$A$1:$C$6" will appear in Step 2
(Excel usually guesses correctly, but if it does not, just amend the range
refs)

Click Next

In Step 3 of the PT wizard

Drag Fruit and drop within Row area
Drag State and drop within Column area

Drag State and drop within Data area
[It will appear as Count of State]

Drag Season and drop within Page area

Click Next > Finish
(or just click Finish - as the default in step 4 is to put the PT in a new
sheet)

The PT will appear in a new sheet to the left of Sheet1

Click on the PT sheet.

The pivot table will appear as:
---------------------------------
Season.........(All) < drop arrow

Count of State..State
Fruit.................florida...georgia...texas...virginia...Grand Total
apples................................................1..........1..........
.....2
grapes......................1...............................................
.......1
oranges...............................................1.....................
......1
peach....................................1..................................
.......1
Grand Total.............1...........1...........2...........1..............5
-----------------------------------

The PT gives all of the summary info for ALL seasons

And if you click on the Season drop menu and select say, Summer
the PT will instantly return the summary details for Summer
And so on.

You can also find excellent lead-ins to Pivot Tables at these 3 links,
amongst others:

Pivot Table Tutorial
By Ed Ferrero at his site:
http://edferrero.m6.net/Pivot.html

Introduction to Pivot Tables in Excel
By Debra Dalgleish at Jon Peltier's site:
http://peltiertech.com/Excel/Pivots/pivotstart.htm

Introduction To Pivot Tables
By Harald Staff at Chip Pearson's site:
http://www.cpearson.com/excel/pivots.htm
 
Back
Top