You're welcome. Thanks for the feedback!

--
Biff
Microsoft Excel MVP


"K****ij" wrote in message
...
Thanks Valko.....

Really Appreciated.....

MAny A times it happens that I know the formula but i don't know how it
works... this time i would know how that worked......

Thanks Again

"T. Valko" wrote:

Let's see how the SUMPRODUCT formula works using this sample data:

...........A..........B..........C
1.......Fe.........Fi.........10
2.......Fo........Fum......22
3.......Foo......Bar.......17
4.......Foo......Gee......42
5.......Bar.......Fly.......19

You want to sum column C where column A = Foo and column B = Bar on the
same
row.

=SUMPRODUCT(--(A1:A5="Foo"),--(B1:B5="Bar"),C1:C5))

Result = 17

Here's how it works...

SUMPRODUCT means the SUM of PRODUCTS. PRODUCTS means to multiply. So,
we're
going to multiply some things and then get the SUM of that
multiplication.

Each of these expressions will return an array of either TRUE (T) or
FALSE
(F):

(A1:A5="Foo")
(B1:B5="Bar")

Fe = Foo = F
Fo = Foo = F
Foo = Foo = T
Foo = Foo = T
Bar = Foo = F

Fi = Bar = F
Fum = Bar = F
Bar = Bar = T
Gee = Bar = F
Fly = Bar = F

SUMPRODUCT works with numbers so we need to convert those logical values,
TRUE and FALSE, to numbers. One way to do that is to use the double unary
"--". It coerces TRUE to numeric 1 and FALSE to numeric 0:

--TRUE = 1
--FALSE = 0

--(A1:A5="Foo")
--(B1:B5="Bar")

--(Fe = Foo) = F = 0
--(Fo = Foo) = F = 0
--(Foo = Foo) = T = 1
--(Foo = Foo) = T = 1
--(Bar = Foo) = F = 0

--(Fi = Bar) = F = 0
--(Fum = Bar) = F = 0
--(Bar = Bar) = T = 1
--(Gee = Bar) = F = 0
--(Fly = Bar) = F = 0

Now, here's where the multiplication takes place.

We coerced the logical test arrays to numbers and the data in col C is
already numbers so now these 3 arrays are multiplied together:

0 * 0 * 10 = 0
0 * 0 * 22 = 0
1 * 1 * 17 = 17
1 * 0 * 42 = 0
0 * 0 * 19 = 0

We have the results of the multiplication (PRODUCTS) so we just add (SUM)
them up:

=SUMPRODUCT({0;0;17;0;0}) = 17

So:

=SUMPRODUCT(--(A1:A5="Foo"),--(B1:B5="Bar"),C1:C5))

Result = 17


--
Biff
Microsoft Excel MVP


"K****ij" wrote in message
...
Hi Valko....

Thanks for the explanation, Also I have another querry if You could
slove
the same for me...

Yesterday in one of the querry asked the reply answer to that was the
following formula...

=SUMPRODUCT(--($A$1:$A$1000=D1),--($B$1:$B$1000=E1),$C$1:$C$1000)

Here can you explain the use of ' -- ' what does that do......?

If that is possible....?

Thanks



"T. Valko" wrote:

=TEXT(A1&"-1","mmmm yyyy")
=TEXT("1-"&A1,"mmmm yyyy")

Depending on your regional date settings Excel will interpret an entry
like
1-2 or 2-1 as a valid date of the current year. For examle, with my
regional
date settings as U.S. English, if I type in cell A1 2-1 Excel
automatically
evaaluates that as a date and displays 1-Feb.

Basically, in the formula we're just taking advantage of Excel's
eagerness
to identify dates!

--
Biff
Microsoft Excel MVP


"yclhk" wrote in message
...
Hi, K****ij,

Actually, i wish to know how does the Date & Text function work in
the
formula ? Why "A1+1" in your formulae and "1-"&A1 in Valko formulae
?

Thanks,


"K****ij" wrote:

Well in may case it was simple that i had formated the cell to just
show
the
month and Year..... Remember we had done-- right click on cell...
Format
and
then Number and Custom...... and TYpe..... "MMMM YYYY", this mad it
to
text,
and the Date formula is the regular date formula..... nothing
else.....
So
chill.....

If you found the answer useful, Please press yes at bottom....
Ok....

"yclhk" wrote:

Hi, K****ij & Valki,

Thanks for your formula. Pls be kind to explain how the Date &
Text
worked
in this formula.

Thanks,


"K****ij" wrote:

Well Check out this formula......
I belive, it should be sufficient......

First custmosize the B1 to format(custom) in that have it as
mmmm
yyyy

Then put the formula in B1 as =Date(2009,A1+1,0)

Well it will return as Feburary 2009,

Now I hope that is helpful and simple....

And if you ask me why I have taken "A1+1" the i am not aware
but
it
works
that way.....

"yclhk" wrote:

How to convert the month in number to the month in text ?
For example :

in cell A1 enter 2
in cell B1 display February 2009

Thanks,