IF function performing logical test on multiple cells

Hi everyone,

So my current problem is that I want to use the IF function to perform the logical test on a column of cells instead of just one cell.

I realize that this can be done by copying the IF function to multiple cells but the way my spreadsheet is currently set up doesn't allow me to do that.

To be more specific, I am trying to use the below function:

Please Login or Register  to view this content.

What I am trying to do is to go through a column of dates, extract the month and compare each month with a specific month. If the date matches up, I want to sum up data in an adjacent field based on another "equal to" criteria.

I know the SUMIF portion is working fine but I am at a lost as to how to perform an IF logical test on a column of cells (compare a column of cells one by one to another specific cell) all in one cell (without having to use multiple cells and summing things up).

Maybe this will be an interesting riddle for you guys =)

Any help or advice is much appreciated!

I forgot to mention that Excel doesn't let me use the IF function on a column of cells, haha. Well, it does but it isn't working as I expect it to as described above.

Maybe this will become clearer after you upload a workbook with some sample data, but I suspect SUMPRODUCT might be your ticket, as in

=SUMPRODUCT(--MONTH(DateRange)=MONTH(CellValue)),SumRange)

or something like that.

Click "Go Advanced" below and then the paper clip icon to upload a (small!) data sample that illustrates what you want to achieve.

cheers

Commendations for wanting to do it on your own. Unless you've already found this one, it's one of the best discussions of SUMPRODUCT on the net

http://www.xldynamic.com/source/xld.SUMPRODUCT.html

The problem lies with the range P1:P9999 in Sheet TD Values containing not just dates but also text and blank values, so the construct

=Date(Year....)) will return #Value! and the whole formula bombs.

Delete the rows with the column headers that separate your monthly data, and give it another go.

I'll get onto this later, but I'll have to feed the kids first. Din-Din time in NZ.

cheers

Given you're using XL2007 you can / should really use SUMIFS function in preference to SUMPRODUCT given it is more efficient (significantly so in truth), eg:

Please Login or Register  to view this content.

In regard to the SUMPRODUCT... the #VALUE! error will as teylyn points out be generated wherever either date ranges or summation ranges contain non numerics - this is because of the explicit coercion being undertaken via the * of the arrays and the use of the MONTH function.

On that basis it's better (IMO) to use double unary (to remove issues re: summation range - no explicit coercion - think SUM(A1:B1) rather than A1+B1) but also to use a TEXT check on the date range rather than a coercion technique - though this is slower (according to Bob - author of the link referenced), eg:

Please Login or Register  to view this content.

The advantage of SUMPRODUCT over SUMIFS is that it is backwards compatible with earlier versions ... if not needed use SUMIFS.
If using SUMPRODUCT I would recommend you look into setting up Dynamic Named Ranges to restrict the size of the ranges being referenced, in the above you are processing nearly 30000 cells - repeated multiple times - will lead to slowdown in performance.

Suggestion:

In your sheet "TD Debit", freeze the panes so that the first three rows stay in place.
Delete the header rows separating the months
If desired, apply conditional formatting to highlight the start of a new month.
Now, this formula in the "Monthly Breakdown" sheet in D4 will work

=SUMPRODUCT((YEAR('TD Debit'!$P$4:$P$9999)=YEAR('Monthly Breakdown'!D3))*(MONTH('TD Debit'!$P$4:$P$9999)=MONTH('Monthly Breakdown'!D3))*('TD Debit'!$C$4:$C$9999="P")*('TD Debit'!$R$4:$R$9999))

Note the difference to your original formula. I got rid of the =Date(Year...,Month...,1) construct, and instead introduced two conditions, one looking at the year and the other one at the month. And the arrays start in row 4 instead of 1, so they don't include cells that will return errors on the date check.

Some general thoughts about your spreadsheet design:

The totalling you want to do, can also be achieved with Pivot tables, which will not eat as much computing power. Pivot tables should actually be the preferred solution, although the result can be achieved with SUMPRODUCT.

SUMPRODUCT, though, especially when it references big ranges, can result in slow calculations.

You are using fixed ranges like P1:P9999 in the Sumproduct arrays. The Sumproduct formula has four arrays to work with, totalling 40000 values to compute for each application of the formula. Don't be surprised if your workbook gets slow.

If you decide against pivot tables, you may be better off if you define dynamic ranges names for your source data instead of hard-coding more cells than you need.

A range name for the data column, for instance could be defined as DebitDates with the formula

=OFFSET('TD Debit'!$P$4,0,0,COUNTA('TD Debit'!$P:$P)-2,1)

Similarly, you can define dynamic ranges for the Dining and Debit columns

DebitDining =OFFSET(DebitDates,0,-13)
Debit =OFFSET(DebitDates,0,2)

These ranges will grow and shrink with the data and will only contain the rows that are actually used ... and won't eat as much processing power when used in a SUMPRODUCT. The formula above could be changed to

=SUMPRODUCT((YEAR(DebitDates)=YEAR('Monthly Breakdown'!D3))*(MONTH(DebitDates)=MONTH('Monthly Breakdown'!D3))*(DebitDining="P")*(Debit))

Makes a little better reading, don't you agree?

Let me know how you get on.

hth

Hi guys,

Just to let you know, my spreadsheet is now working and is much sexier than before, haha!

Thanks for telling me about SUMPRODUCT and freezing panes and Dynamic Range Names!

Excel has a lot more functionality than I originally thought. =)

You guys are awesome, keep helping people out! =D