If Function to sun various categories

[cornes91]

Good evening,

I am finding problems (probably in my convaluted IF Functions (see example below)) at counting various categories on my income and expenditure sheets.

The category is Column C on my Income and Expenditure sheets. This is then counted on 'Sheet 2' and returned on the Balance Sheet.

Can anyone recommend a more simplified formula that would return the correct amounts?

Currently my IF functions look horrifically like this: (as on the attached document.
=(IF(Expenditure!C3=Sheet2!A13,(SUM(Expenditure!D3:E3)),0))+(IF(Expenditure!C4=Sheet2!A13,(SUM(Expenditure!D4:E4)),0))+(IF(Expenditure!C5=Sheet2!A13,(SUM(Expenditure!D5:E5)),0))+(IF(Expenditure!C6=Sheet2!A13,(SUM(Expenditure!D6:E6)),0))+(IF(Expenditure!C7=Sheet2!A13,(SUM(Expenditure!D7:E7)),0))+(IF(Expenditure!C8=Sheet2!A13,(SUM(Expenditure!D8:E8)),0))+(IF(Expenditure!C9=Sheet2!A13,(SUM(Expenditure!D9:E9)),0))+(IF(Expenditure!C10=Sheet2!A13,(SUM(Expenditure!D10:E10)),0))+(IF(Expenditure!C11=Sheet2!A13,(SUM(Expenditure!D11:E11)),0))+(IF(Expenditure!C12=Sheet2!A13,(SUM(Expenditure!D12:E12)),0))+(IF(Expenditure!C13=Sheet2!A13,(SUM(Expenditure!D13:E13)),0))+(IF(Expenditure!C14=Sheet2!A13,(SUM(Expenditure!D14:E14)),0))+(IF(Expenditure!C15=Sheet2!A13,(SUM(Expenditure!D15:E15)),0))+(IF(Expenditure!C16=Sheet2!A13,(SUM(Expenditure!D16:E16)),0))+(IF(Expenditure!C17=Sheet2!A13,(SUM(Expenditure!D17:E17)),0))+(IF(Expenditure!C18=Sheet2!A13,(SUM(Expenditure!D18:E18)),0))+(IF(Expenditure!C19=Sheet2!A13,(SUM(Expenditure!D19:E19)),0))+(IF(Expenditure!C20=Sheet2!A13,(SUM(Expenditure!D20:E20)),0))+(IF(Expenditure!C21=Sheet2!A13,(SUM(Expenditure!D21:E21)),0))+(IF(Expenditure!C22=Sheet2!A13,(SUM(Expenditure!D22:E22)),0))+(IF(Expenditure!C23=Sheet2!A13,(SUM(Expenditure!D23:E23)),0))+(IF(Expenditure!C24=Sheet2!A13,(SUM(Expenditure!D24:E24)),0))+(IF(Expenditure!C25=Sheet2!A13,(SUM(Expenditure!D25:E25)),0))+(IF(Expenditure!C26=Sheet2!A13,(SUM(Expenditure!D26:E26)),0))+(IF(Expenditure!C27=Sheet2!A13,(SUM(Expenditure!D27:E27)),0))+(IF(Expenditure!C28=Sheet2!A13,(SUM(Expenditure!D28:E28)),0))+(IF(Expenditure!C29=Sheet2!A13,(SUM(Expenditure!D29:E29)),0))+(IF(Expenditure!C30=Sheet2!A13,(SUM(Expenditure!D30:E30)),0))+(IF(Expenditure!C31=Sheet2!A13,(SUM(Expenditure!D31:E31)),0))+(IF(Expenditure!C32=Sheet2!A13,(SUM(Expenditure!D32:E32)),0))+(IF(Expenditure!C33=Sheet2!A13,(SUM(Expenditure!D33:E33)),0))+(IF(Expenditure!C34=Sheet2!A13,(SUM(Expenditure!D34:E34)),0))+(IF(Expenditure!C35=Sheet2!A13,(SUM(Expenditure!D35:E35)),0))+(IF(Expenditure!C36=Sheet2!A13,(SUM(Expenditure!D36:E36)),0))+(IF(Expenditure!C37=Sheet2!A13,(SUM(Expenditure!D37:E37)),0))+(IF(Expenditure!C38=Sheet2!A13,(SUM(Expenditure!D38:E38)),0))+(IF(Expenditure!C39=Sheet2!A13,(SUM(Expenditure!D39:E39)),0))+(IF(Expenditure!C40=Sheet2!A13,(SUM(Expenditure!D40:E40)),0))+(IF(Expenditure!C41=Sheet2!A13,(SUM(Expenditure!D41:E41)),0))+(IF(Expenditure!C42=Sheet2!A13,(SUM(Expenditure!D42:E42)),0))+(IF(Expenditure!C43=Sheet2!A13,(SUM(Expenditure!D43:E43)),0))+(IF(Expenditure!C44=Sheet2!A13,(SUM(Expenditure!D44:E44)),0))+(IF(Expenditure!C45=Sheet2!A13,(SUM(Expenditure!D45:E45)),0))+(IF(Expenditure!C46=Sheet2!A13,(SUM(Expenditure!D46:E46)),0))+(IF(Expenditure!C47=Sheet2!A13,(SUM(Expenditure!D47:E47)),0))+(IF(Expenditure!C48=Sheet2!A13,(SUM(Expenditure!D48:E48)),0))+(IF(Expenditure!C49=Sheet2!A13,(SUM(Expenditure!D49:E49)),0))+(IF(Expenditure!C50=Sheet2!A13,(SUM(Expenditure!D50:E50)),0))+(IF(Expenditure!C51=Sheet2!A13,(SUM(Expenditure!D51:E51)),0))+(IF(Expenditure!C52=Sheet2!A13,(SUM(Expenditure!D52:E52)),0))+(IF(Expenditure!C53=Sheet2!A13,(SUM(Expenditure!D53:E53)),0))+(IF(Expenditure!C54=Sheet2!A13,(SUM(Expenditure!D54:E54)),0))+(IF(Expenditure!C55=Sheet2!A13,(SUM(Expenditure!D55:E55)),0))+(IF(Expenditure!C56=Sheet2!A13,(SUM(Expenditure!D56:E56)),0))+(IF(Expenditure!C57=Sheet2!A13,(SUM(Expenditure!D57:E57)),0))+(IF(Expenditure!C58=Sheet2!A13,(SUM(Expenditure!D58:E58)),0))+(IF(Expenditure!C59=Sheet2!A13,(SUM(Expenditure!D59:E59)),0))+(IF(Expenditure!C60=Sheet2!A13,(SUM(Expenditure!D60:E60)),0))+(IF(Expenditure!C61=Sheet2!A13,(SUM(Expenditure!D61:E61)),0))+(IF(Expenditure!C62=Sheet2!A13,(SUM(Expenditure!D62:E62)),0))+(IF(Expenditure!C63=Sheet2!A13,(SUM(Expenditure!D63:E63)),0))+(IF(Expenditure!C64=Sheet2!A13,(SUM(Expenditure!D64:E64)),0))+(IF(Expenditure!C65=Sheet2!A13,(SUM(Expenditure!D65:E65)),0))+(IF(Expenditure!C66=Sheet2!A13,(SUM(Expenditure!D66:E66)),0))+(IF(Expenditure!C67=Sheet2!A13,(SUM(Expenditure!D67:E67)),0))+(IF(Expenditure!C68=Sheet2!A13,(SUM(Expenditure!D68:E68)),0))+(IF(Expenditure!C69=Sheet2!A13,(SUM(Expenditure!D69:E69)),0))+(IF(Expenditure!C70=Sheet2!A13,(SUM(Expenditure!D70:E70)),0))+(IF(Expenditure!C71=Sheet2!A13,(SUM(Expenditure!D71:E71)),0))+(IF(Expenditure!C72=Sheet2!A13,(SUM(Expenditure!D72:E72)),0))+(IF(Expenditure!C73=Sheet2!A13,(SUM(Expenditure!D73:E73)),0))+(IF(Expenditure!C74=Sheet2!A13,(SUM(Expenditure!D74:E74)),0))+(IF(Expenditure!C75=Sheet2!A13,(SUM(Expenditure!D75:E75)),0))+(IF(Expenditure!C76=Sheet2!A13,(SUM(Expenditure!D76:E76)),0))+(IF(Expenditure!C77=Sheet2!A13,(SUM(Expenditure!D77:E77)),0))+(IF(Expenditure!C78=Sheet2!A13,(SUM(Expenditure!D78:E78)),0))+(IF(Expenditure!C79=Sheet2!A13,(SUM(Expenditure!D79:E79)),0))+(IF(Expenditure!C80=Sheet2!A13,(SUM(Expenditure!D80:E80)),0))+(IF(Expenditure!C81=Sheet2!A13,(SUM(Expenditure!D81:E81)),0))+(IF(Expenditure!C82=Sheet2!A13,(SUM(Expenditure!D82:E82)),0))+(IF(Expenditure!C83=Sheet2!A13,(SUM(Expenditure!D83:E83)),0))+(IF(Expenditure!C84=Sheet2!A13,(SUM(Expenditure!D84:E84)),0))+(IF(Expenditure!C85=Sheet2!A13,(SUM(Expenditure!D85:E85)),0))+(IF(Expenditure!C86=Sheet2!A13,(SUM(Expenditure!D86:E86)),0))+(IF(Expenditure!C87=Sheet2!A13,(SUM(Expenditure!D87:E87)),0))+(IF(Expenditure!C88=Sheet2!A13,(SUM(Expenditure!D88:E88)),0))+(IF(Expenditure!C89=Sheet2!A13,(SUM(Expenditure!D89:E89)),0))+(IF(Expenditure!C90=Sheet2!A13,(SUM(Expenditure!D90:E90)),0))+(IF(Expenditure!C91=Sheet2!A13,(SUM(Expenditure!D91:E91)),0))+(IF(Expenditure!C92=Sheet2!A13,(SUM(Expenditure!D92:E92)),0))+(IF(Expenditure!C93=Sheet2!A13,(SUM(Expenditure!D93:E93)),0))+(IF(Expenditure!C94=Sheet2!A13,(SUM(Expenditure!D94:E94)),0))+(IF(Expenditure!C95=Sheet2!A13,(SUM(Expenditure!D95:E95)),0))+(IF(Expenditure!C96=Sheet2!A13,(SUM(Expenditure!D96:E96)),0))+(IF(Expenditure!C97=Sheet2!A13,(SUM(Expenditure!D97:E97)),0))+(IF(Expenditure!C98=Sheet2!A13,(SUM(Expenditure!D98:E98)),0))+(IF(Expenditure!C99=Sheet2!A13,(SUM(Expenditure!D99:E99)),0))

[AliGW]

All you need is this:

=SUMPRODUCT((Expenditure!$C$3:Expenditure!$C$99=A13)*Expenditure!$D$3:Expenditure!$E$99)

[AliGW]

If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED.

[shank_mis]

ON Sheet2, Cell C2:

Please Login or Register  to view this content.

Confirm with Ctrl+Shift+Enter
Drag the formula down.

[menem]

I think you may want to split it into each year summary.
So, I insert 3 rows and use this formula (and add start date/end date in row 1,2)

C5

Please Login or Register  to view this content.

C16

Please Login or Register  to view this content.

Regards.