Formula help

Closed
rgreen Posts 1 Registration date Tuesday October 29, 2013 Status Member Last seen October 29, 2013 - Oct 29, 2013 at 10:28 AM
TrowaD Posts 2921 Registration date Sunday September 12, 2010 Status Moderator Last seen December 27, 2022 - Oct 29, 2013 at 01:00 PM
I have a huge excel spreadsheet that I am needing to sort by type of loan (J3:J132) and then select the totals from that type of loan (D3:D132) and total them together but only if (F3:F132) is <89. Eaxh loan type in J column is listed individually in S Column. For example if I want all loans coded a 140 in J column I have a spot for it at S1

1 response

TrowaD Posts 2921 Registration date Sunday September 12, 2010 Status Moderator Last seen December 27, 2022 552
Oct 29, 2013 at 01:00 PM
Hi RGreen,

Do this experiment to understand array formula's:

A1: 5
A2: 5
A3: 5

B1: F
B2: empty
B3: F

Question: Add column A if column B = "F"
Answer: 10
Array formula: =SUM(IF(B1:B3="F",1,0)*A1:A3)
NOTE: Array formula's need to be confirmed by hitting Ctrl+Shift+Enter. If done correctly the formula will be enclosed by curly brackets.

Best regards,
Trowa