software

HI ALL.

This is my first post, requesting your help, after trying out various
suggestions.

In the following data, column 'A' is month (text) amp; column 'B' contains
training programme codes, (numeric).

I want to get the total of column 'C' amp; column 'D' respectively, for
particular values specified for column 'A' amp; 'B', (As noted in the
first line - ie. for MNTH=Jun-06 amp; PROG=8012):

A B C D
Jun-06 8012 55 0

MNTH PROG POST ATTE
Apr-06 801130 25
May-06 801228 27
May-06 701029 28
Apr-06 701030 28
Jun-06 801225 29
Jun-06 801130 28I didn't succeed in getting result for
=SUM(IF((A4:A9=A1) (B4:B9=B1),C4:C8)
: as was suggested in a popular computer magazine. The result returned
was #VALUE in the first part of the logic, (A4:A9=A1).

I tried the option:
=SUMPRODUCT(--(A3:A9=A1),--(B3:B9=B1),D39)
as suggested in several cases, but the value returned was '0' - as
noted in the first line

I WILL BE THANKFUL FOR ANY GUIDANCE IN THE
MATTER, THAT WILL HELP ME IN BETTER USE OF THE DATA IN MY WORK
AREA.
THANKS.

JITU--
jitu_tikili
------------------------------------------------------------------------
jitu_tikili's Profile: www.excelforum.com/member.php...oamp;userid=33119
View this thread: www.excelforum.com/showthread...hreadid=529327Try =SUMPRODUCT(($A$4:$A$9=$A$1)*($C$4:$C$9=$C$1)*(D4: D9))

- JohnActually, that should be the following. I had an extra column of data
in my spreadsheet.

Try =SUMPRODUCT(($A$4:$A$9=$A$1)*($B$4:$B$9=$B$1)*(C4: C9))

- John
Hi
The first formula you mention is supposed to be an array formula. After
you type these formula you have to press together Ctrl Shift Enter.
Excel will wrap the formula with quot;{quot; and quot;}quot;. If you fail to do this,
you get an error (#VALUE!).
But in your case (I hope to get it right) you can use the SUMPRODUCT
function, which is not an array function.
If you want to retrieve the sum of values when both conditions are
true, use this formula for Total POST (column C)
=SUMPRODUCT(((A5:A10)=A2)*1,((B5:B10)=B2)*1,C5:C10 )
and this for column D
=SUMPRODUCT(((A5:A10)=A2)*1,((B5:B10)=B2)*1,D510 )
You can find an explanation about sumproduct and array formulas in my
blog (see in the signature)--
jordun------------------------------------------------------------------------
jordun's Profile: www.excelforum.com/member.php...oamp;userid=33118
View this thread: www.excelforum.com/showthread...hreadid=529327
Thank you friends.

JITU--
jitu_tikili
------------------------------------------------------------------------
jitu_tikili's Profile: www.excelforum.com/member.php...oamp;userid=33119
View this thread: www.excelforum.com/showthread...hreadid=529327
*I tried with the above options amp; succeeded *- but within the same
worksheet.

I request your help for the following:Even in the same worksheet - while trying
=SUMPRODUCT(((A5:A10)=A2)*1,((B5:B10)=B2)*1,C5:C10 )
sometimes I got #Value amp; sometimes #NA.

I am not sure - whether that is due to the fact that when I write
apr-06, in the column, it automatically gets converted to Apr-06 with
right alignment, indicating date format. The validating value however
was based on dropdown list using DATA--gt;gt; VALIDATION option. Trying
to find out the error, it indicated data type mismatch. When I tried
to convert the contents to text also - it didn't succeed - since
Apr-06 got converted as 38808. This left the column cells with no. amp;
the validation cell with the text.- Further I could not succeed in getting the sum value of column 'C'
of Sheet1, in a cell in Sheet2, based on sumproduct option used to
validate column 'A' amp; 'B' values of Sheet1.Kindly guide me.

jitu--
jitu_tikili
------------------------------------------------------------------------
jitu_tikili's Profile: www.excelforum.com/member.php...oamp;userid=33119
View this thread: www.excelforum.com/showthread...hreadid=529327