Help for sumif / sumproduct

[jitu_tikili]

HI ALL

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

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

I want to get the total of column 'C' & column 'D' respectively, fo
particular values specified for column 'A' & 'B', (As noted in th
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 returne
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' - a
noted in the first line

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

JIT

 

[John Michl]

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

- John

 

[John Michl]

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

 

[jordun]

Hi
The first formula you mention is supposed to be an array formula. Afte
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 SUMPRODUC
function, which is not an array function.
If you want to retrieve the sum of values when both conditions ar
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 m
blog (see in the signature

 

[jitu_tikili]

Thank you friends.

JITU

 

[jitu_tikili]

*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