MrExcel Publishing
Your One Stop for Excel Tips & Solutions

If function


Posted by Dee Williamson on July 03, 2001 6:04 AM

I am trying to add figures in the last column based on two conditions
in the spreadsheet. Here is an example:

Nebraska LDD DESCR $100.00
Arizona BRD DESCR $250.00
Nebraska LDD DESCR $ 75.00
Nebraska FTD DESCR $125.00

I want to get all the totals for "Nebraska" and "LDD".
I can get as far as totaling the figures for NE but
it does not break down further. Any ideas?


Posted by Aladin Akyurek on July 03, 2001 6:17 AM

Multiconditional Sum

Dee,

Try the following array formula:

=SUM((A2:A5="Nebraska")*(B2:B5="LDD")*(D2:D5))

You need to hit CONTROL+SHIFT+ENTER (not just ENTER) to enter this formula.

Note 1. You may use cell references instead of the values such as "Nebraska" in this formula.

Note 2. This question is essentially of the same nature as questions a multiconditional count. The most recent thread about the latter involves also a solution without array formulas.

Note 3. You might also like to investigate Excel database functions wtr this type of questions.

Aladin