Complex formula

[Alexey]

Hi Everyone

I am trying to achieve the following

Sheet 2 contains data imported from an Access Spreadsheet and I am trying to
analyise this data on Sheet 1

Sheet1!A1 contains date field in mmm-yy format

Sheet2!Column A is a date field in mmm-yy format and contains some 7500 rows
growing at 200 per month
Sheet2!Column B contains one of four string text fields
Sheet2!Column C contains a numeric value

I want to try and create a formula that will set Sheet1!B1 to the sum of
Sheet2!Column C where a) Sheet2!Column A is the same as Sheet1A1 (i.e. the
same month and year) and b) Sheet2!Column B is equal to "Text1"

I will then simply replciate this formula across all four text string values
and this will give me the sums I need for all four texts.

I am having a real issue trying to resolve this - can anyone help please

thanks

 

[David Biddulph]

=SUMPRODUCT(--(Sheet2!A1:A65535=Sheet1!A1),--(Sheet2!B1:B65535="AAA"),Sheet2!C1:C65535)

 

[Alexey]

David, very many thanks - I dont think i would have ever got to that !
I am not sure what the two dashes are for between the arrays - i tried
looking on Help but couldn't find anything yet

I am still having a problem - I have replaced the syntax with the actual
references but I am getting a 0 returned, when from the data there should be
18.

Can you just confirm that what i have is OK:

=SUMPRODUCT(--('Data from MIS'!M4:M65535=A5),--('Data from
MIS'!N4:N65535="Not Moderated"),'Data from MIS'!P465535)


Thanks
A

 

[David Biddulph]

The two dashes are what is known as a double unary minus, and convert a TRUE
or FALSE to 1 or 0 so that you can do arithmetic such as the multiplication.

Are you sure that the dates in your column M and in A5 are both actual
dates, and that one of them isn't text? [Try reformatting temporarily and
see whether the date format changes.]
Are you sure that your column N hasn't got spurious spaces or other
non-printing characters before or after the "Not Moderated"? Does
=LEN(Nnn) give you the length of string you expect? 13 in this case?
Are you sure that the contents of column P are numbers, and not text?

If you're not getting the result you expect, break the formula down and look
at each element individually. For example, if you think that row 99
satifies your conditions, look at what formulae such as the following give:
='Data from MIS'!M99=A5
='Data from MIS'!N99="Not Moderated"