sum columns only if date

[dkte737]

Hi, I need a little help with this function.

I have dates in column A (3/31/12), an categories in column
(maintenance) and then a dollar amount in column C ($52.55).

I would like a function that would sum the dollar amounts only if th
date was in March and the catagory matched "maintnenance"

Any help?

Thanks,

Davi

 

[isabelle]

hi David,

=SUMPRODUCT(--(MONTH($A$2:$A$100)=3)*($B$2:$B$100="maintnenance")*($C$2:$C$100))


--
isabelle



Le 2012-06-10 09:39, dkte737 a écrit :

 

[dkte737]

I've used SUMPRODUCT in the attached. It can also be done with SUMIF
but it's far less easy to read.

Hope it makes sense. Feel free to clarify if not.

That worked! Ok, simple question....how did you get your top row (DATE
to not move when you sort oldest to newest?

Davi

+-------------------------------------------------------------------
+-------------------------------------------------------------------

 

[joeu2004]

=SUMPRODUCT(--(MONTH($A$2:$A$100)=3)*($B$2:$B$100="maintnenance")*($C$2:$C$100))

@Isabelle....

There is no need for the double negative (--) in this context.

The purpose of the double negative in the
expression --(MONTH($A$2:$A$100)=3) is to convert TRUE and FALSE (the result
of the comparison) to 1 and 0 which is required for SUMPRODUCT to work as
intended.

But __any__ arithmetic will perform that conversion. So the expression
(MONTH($A$2:$A$100)=3)*($B$2:$B$100="maintnenance") is sufficient to "and"
the two conditions and produce 1 or 0. No double negative is needed.

@David: We cannot write simply
AND(MONTH($A$2:$A$100)=3,$B$2:$B$100="maintnenance") in the context of
SUMPRODUCT because AND will process the entire arrays and produce a single
value instead of an array which is required for SUMPRODUCT to work as
intended.

There is nothing wrong (otherwise) with the SUMPRODUCT expression above.
However, the following is arguably more flexible and it is more efficient
when C2:C100 is expected to contain numeric values, as David stated:

=SUMPRODUCT((MONTH($A$2:$A$100)=3)*($B$2:$B$100="maintnenance"),$C$2:$C$100)

If some of C2:C100 might contain non-numeric values (notably the null
string, ""), SUMPRODUCT treats them as zero, whereas SUMPRODUCT(...*C2:C100)
will result in an Excel error.

It is not uncommon to encounter null strings among columns of numbers in
templates, for example.

 

[GS]

<FWIW>
In my personal bookkeeping app I use 'FiscalPeriod' columns to the
right of (Income/Expense) transaction details, one column for each
month of the fiscal year. The row above the month labels containing
their respective indexes (ie:Jan=1,Feb=2...). To capture transaction
amounts into their respective month columns I use the following
formula...

=IF(OR(Date="",MONTH(Date)<>MonthIndex),"",IF(CurrencyType<>"",Amount*ExchRate,Amount))

...which leaves empty cells blank for a cleaner look to the section (I
hate looking at zeros when not needed). You could modify it for your
needs by implementing the month indexes concept without the defined
names as long as the correct col/row abs/rel refs are used. *Note* that
all names have local scope (sheet-level) because they are duplicated on
both the Income ledger sheet and the Expenses ledger sheet.

'Date' is a col-absolute/row-relative dynamic defined name range for
entering the transaction date.

'MonthIndex' is a col-relative/row-absolute defined name that specifies
the where the month indexes are stored. (This is hidden by matching
font color to fill color)

'CurrencyType' is a col-absolute/row-relative defined name used for
'flagging' foreign currency amounts via a DV dropdown. To the right is
('ExchRate') where the foreign currency exchange rate is entered.

'Amount' is a col-absolute/row-relative defined name used for the
transaction 'items' total (excluding taxes). (Transactions may use
multiple records (each row is a 'transaction record') if the items are
spread over more than one accounting 'Category' or 'Subcategory')

For maintenance simplicity, the above formula is stored in a defined
name ("PeriodAmount") so I only need to make changes in one place (per
sheet where defined/used). In this case, the formula entry for all
cells in the fiscal period section is...

=PeriodAmount

To use the formula without defined names...

=IF(OR($B6="",MONTH($B6)<>Q$4),"",IF($H6<>"",$J6*$I6,$I6))

...where the first transaction starts in row 6, month indexes are in row
4, and the fiscal period section starts in col "Q". You could copy it
down and across OR preselect the entire area and use the keyboard combo
'Ctrl+Enter' to populate all selected cells. (I use a 'BlankRecord'
[hidden row] for inserting more rows where/when needed, and so the 12
formulas are already in place)

IMO, using defined names makes things easier to understand. (I just
hate having to trace refs to see what/where they point to when it's so
much easier to use self-describing names)

--
Garry

Free usenet access at http://www.eternal-september.org
ClassicVB Users Regroup!
comp.lang.basic.visual.misc
microsoft.public.vb.general.discussion

 

[Gord Dibben]

In Sort Options enable "my data range has header row"


Gord