Conditional formula

[colt seavers]

Hi

I've got a spreadsheet which lists amounts of records per year against two separate criteria.

I'd like a formula to total the no. of records based on criteria 1 in one column and criteria 2 in another column also based on year.

Having real trouble trying to figure this one out

This is what it looks like:

A B C D
Year No of records Criteria 1 Criteria 2
2003 5 Y
2005 10000 Y


I'd like a formula which looksup whether there's a Y in D, that the year corresponds to the total year and then returns the total records from B.

The results would be as follows:
Totals:
Year Criteria 1 Criteria 2
2003 0 5
2005 10000 0

Any help?

Apologies for the formatting of the message !!! The attached file should show a bit better.

[EllBol]

Hi,

If I have uderstood you correctly, you need two slightly different formulae.

In B10 use: =IF(C2="Y",B2,0) and copy down.

In C10 use: =IF(D2="Y",B2,0)

Hope this is what tou are looking for!

EllBol

[colt seavers]

What I've ended up doing is putting in another column with:

=IF(C2="Y",B2,0)
=IF(D2="Y",B2,0)

and copied that down - as you recommended (thanks).

to calculate record totals based on criteria 1 and 2.

and then in my totals:

=SUMIF(A:A, "Date",E:E )
=SUMIF(A:A, "Date",F:F )

To produce the numbers per year - I've then hidden E & F on the data sheet.

The totals are being pulled across to the summary sheet.

I was wondering if it was possible to do this in one formula without creating the extra E & F columns on the source data.

But it works anyway so thanks !!!!

Anyone who has a single formula solution I would be interested in how this works.

[EllBol]

I have assumed your 'No of records' is in B2:B14. This can be extended as far as needed, and can cover empty cells, but all columns MUST be same length.

For results:
Year 2003 is in F2 annd heading Criteria 1 & 2 in G1, H1

In G2 enter the formula:

=SUMPRODUCT(($A$2:$A$14=$F2)*1,$B$2:$B$14,(C$2:C$14="Y")*1)

Copy across and down.

I think that is what you require!

EllBol