Multi selection of an array, ending with sum

[sammberg]

*Multi selection within an array, ending with sum*: what combination o
functions do I need to use to select a subset within an array and the
select within this subset a new selection which I can then sum a colum
within this last subset.

For example If I have an array of 3 columns and 100 rows and I selec
criteria based on 1st column, which produces a new smaller array with
columns and 60 rows, I then need to select within this smaller array
criteria based on 2nd column, which produces an even smaller subse
array from which I sum on the 3rd colum

 

[Bernie Deitrick]

Sam,

=SUMPRODUCT((A1:A100="Whatever")*(B1:B100="And This")*(C1:C100))

Or you can filter and use
=SUBTOTAL(9,C1:C100)

HTH,
Bernie
MS Excel MVP

 

[sammberg]

Thanks Bernie for you quick reply, however this will not do it for me.

I will explain my problem like this : I have a spreadsheet that
contains purchases and in this purchases spreadsheet I am trying for
example to sum all the purchase in Febuary with cost center of
"goods".

What this means I have 3 columns : a month column, a cost center column
and an Amounts column. Also they are not sorted in any order.

So I am trying to select the following:
1. All enteries in Febuary in the month column i.e month=2
2. From within the above selection all the cost center "goods" =9
3. From the result of the above, to sum the Amounts column = the total
purchases made in FEB with a cost center =9 (goods)

I hope this has clarified what I am trying to achive.

 

[Dave Peterson]

=sumproduct(--(isnumeric(a1:a100)),--(month(a1:a100)=2),
--(c1:c100=9),(f1:f100))
(all one line)

If column A contained the date
C contained the cost center
F contained the amounts

Make the range big enough, but don't use the whole column)

=sumproduct() likes to work with numbers. The -- stuff converts trues/falses to
1's and 0's.

The isnumeric(a1:a100) makes sure that empty cells aren't treated as 0's (which
would be seen as having a month of 1 (January)).

You may want to take a look at data|Pivottable, too.

It might be easier to get a table of all the possibilities.

 

[sammberg]

Hi Dave

Appreciate your input, used formulea below result is #VALUE.

=SUMPRODUCT(Purchases!S5:S100=2,Purchases!B5:B100=9,"Purchases!Purchases!G5:G5100")

The "Month" is column is "S", Cost Center column "B" and Amount Column
"G" would there position relative to each other have an effect.

I will look a pivot tables also perhaps the answer is their.

 

[Dave Peterson]

=SUMPRODUCT(--(Purchases!S5:S100=2),--(Purchases!B5:B100=9),
--(Purchases!G5:G100"))

All one cell.