Help for sumif / sumproduct
 

HI ALL.

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

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

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

A B C D
Jun-06 8012 55 0

MNTH PROG POST ATTE
Apr-06 8011 30 25
May-06 8012 28 27
May-06 7010 29 28
Apr-06 7010 30 28
Jun-06 8012 25 29
Jun-06 8011 30 28


I 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),D3:D9)
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: http://www.excelforum.com/member.php...o&userid=33119
View this thread: http://www.excelforum.com/showthread...hreadid=529327

Help for sumif / sumproduct
 

Try =SUMPRODUCT(($A$4:$A$9=$A$1)*($C$4:$C$9=$C$1)*(D4: D9))

- John

Help for sumif / sumproduct
 

Actually, 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

Help for sumif / sumproduct
 

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 "{" and "}". 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,D5:D10 )
You can find an explanation about sumproduct and array formulas in my
blog (see in the signature)


--
jordun


------------------------------------------------------------------------
jordun's Profile: http://www.excelforum.com/member.php...o&userid=33118
View this thread: http://www.excelforum.com/showthread...hreadid=529327

Help for sumif / sumproduct
 

Thank you friends.

JITU


--
jitu_tikili
------------------------------------------------------------------------
jitu_tikili's Profile: http://www.excelforum.com/member.php...o&userid=33119
View this thread: http://www.excelforum.com/showthread...hreadid=529327

Help for sumif / sumproduct
 

*I tried with the above options & 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 & 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-- 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. &
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' & 'B' values of Sheet1.


Kindly guide me.

jitu


--
jitu_tikili
------------------------------------------------------------------------
jitu_tikili's Profile: http://www.excelforum.com/member.php...o&userid=33119
View this thread: http://www.excelforum.com/showthread...hreadid=529327