Assuming real dates in A5:A10, and 1st-of-month dates placed in H3, J3, K3.
H3:I3, J3:K3, L3:M3 are assumed formatted to "Center across selection" via
Format Cells Alignment tab Text alignment Horiz droplist (last
selection)
A sample construct is available at:
http://www.savefile.com/files/7207078
Summarizing by month n product code via sumproduct.xls
In H5, copied to I5, filled down to I8:
=SUMPRODUCT((TEXT($A$5:$A$10,"mmm-yy")=TEXT($H$3,"mmm-yy"))*($E$5:$E$10=$G5),B$5:B$10)
In J5, copied to K5, filled down to K8:
=SUMPRODUCT((TEXT($A$5:$A$10,"mmm-yy")=TEXT($J$3,"mmm-yy"))*($E$5:$E$10=$G5),B$5:B$10)
In L5, copied to M5, filled down to M8:
=SUMPRODUCT((TEXT($A$5:$A$10,"mmm-yy")=TEXT($L$3,"mmm-yy"))*($E$5:$E$10=$G5),B$5:B$10)
The 3 formulas above are similar, except pointing to H3, J3, L3 respectively
For a neater look, extraneous zeros are suppressed from displaying in the
sheet via clicking Tools Options View tab Uncheck "Zero values" OK
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---
" wrote:
Hello
I am rather newbie in excel and need a help with a certain problem I
have.
On the following url (http://www.fuchka.info/tmp/excel.jpg) You can see
a screencapture of excel worksheet and sample data. I need a formula
for right table which will scan left table and:
1. compare the "date" (example: columns "H" and "I" should get values
only for month June) and then
2. compare the "code" (example: rows in column "G" should return values
(from income/outcome cells) corresponding to code number in a row in
column E.
So .. how do I get "I5" cell ...
I check column A for a date in range from June 1st to June 31st, after
that I take ALL VALUES from column C for which in column E is code 100.
So in my case I have only one value ($100,00). If i would have more
then one value, the function should AutoSum them. And now in cell "I5"
I get "$100,00".
so one more time ...
function should check for a specific date range (month) and then for
specific code (100 or 101 or 102 or etc) and return SUM of all values
in corespoding columns "B" or "C" (depends on income or outcome
values).
(outcome: if date=june and code=100 return all values from C and
autosum them)
So can anybody help me with this problem ...?
And sorry for my bad english .. :D