Help: SearchAndGet a value Help

[fuchka]

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 ..

 

[Toppers]

for H5:

=SUMPRODUCT(--(MONTH($A$5:$A$1000)=6),--($E$5:$E$1000=$G5),($B$5:$B$1000))

for I5:

=SUMPRODUCT(--(MONTH($A$5:$A$1000)=6),--($E$5:$E$1000=$G5),($C$5:$C$1000))

Copy both down as far as required, changing 1000 as required

For columns J & K, copy the above changing 6 to 7
For columns L & M, copy the above changing 6 to 8
etc

HTH

 

[Max]

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

 

[fuchka]

THANK YOU! :d

for H5:

=SUMPRODUCT(--(MONTH($A$5:$A$1000)=6),--($E$5:$E$1000=$G5),($B$5:$B$1000))

for I5:

=SUMPRODUCT(--(MONTH($A$5:$A$1000)=6),--($E$5:$E$1000=$G5),($C$5:$C$1000))

Copy both down as far as required, changing 1000 as required

For columns J & K, copy the above changing 6 to 7
For columns L & M, copy the above changing 6 to 8
etc

HTH

 

[fuchka]

just one more thing please ... it just came up ...

is it possible in the same way to set code (example: 100) and date
(example: month June) and get a list of entire rows with all values?

example from my previous table:

I enter the code value: "100" and month: "6"

and get all the rows with code 100 and month June

date / income / outcome / description / code
6 / $150 / / Bla Bla / 100 /
6 / / $100 / Bla Bla / 100

thnx