Calculating Formulas in SUMIF/COUNTIF? (Excel 97-SR2)

Topic

New Reply

Hi Folks

First post…. Searched message board without success, which I appreciate doesn’t mean my question isn’t already answered there..

Ok, What I’m trying to do is to do away with an intermediate worksheet that calculates financial values over a 104 week period. Elsewhere I summarise these values based upon financial quarters and/or a specific criteria entered. I’m only interested in the summary, I have no real use for the individual 104 week calculations.

Each of the 104 weekly calculations are calculated thus:-

=IF(ISNUMBER(PlanView!D5),((PlanView!D5*(IF(IncludeDailyCost,Planning!$DF5,0)+IF(IncludeTandS,Planning!$DG5,0)))*5)*D$4,””)

There are 40 rows. (Worse, there are two worksheets with similar calculations.)

Elsewhere I will summarise each row something like:

=IF(PlanViewCost!B5=”SelectedValue”, SUM(D5:DD5),0)

What I ‘think’ I ought to be able to do is perform the first calculation above inside the SUM(D5:DD5) in the second calculation.

Is this possible?

Regards
Peter

Reply | Quote

Replies

You should be able to do it with ARRAY formulas, relatively simply

. I am a little confused about exactly what formulas you want to get rid of/combine.

What sheet is (sumD5:DD5) from?
I assume tha IncludeDailyCost and IncludeTandS are range names that contain true or False.
What is “SelectedValue” it does not seem to be a range name (since it is in quotes), I was thinking it might be a generic reference to a value, but then why not a cell name or range name?

Could you post an example spreadsheet with proprietary info deleted? I was trying to set something up, but I got confused on some of the finer details.

You could note what the “temp column calcs” are that you don’t need and what sums of these you want to keep.

Steve

Reply | Quote

Hi Steve

OK.. I’ve attached a stripped down version of two of the worksheets from the model that I am working on, with comments. Hopefully this should make my intentions somewhat clearer. Any help gretly appreciated.

Regards
Peter

Reply | Quote

If I understand correctly:

Formula D21 could be replaced with(all one line, confirm w/ ctrl-shift enter NOT enter):

=SUMPRODUCT((PlanView!D5:P5),$D$4:$P$4,IF(D$3:P$3>=$D$20,1,0))*(IncludeDailyCost*$S5+IncludeTandS*$T5)*5

This can be copied down d22, d23, etc.
This is the sum >= the date in D20.

If you want the sum >=D20 and =$D$20,1,0))-SUMPRODUCT((PlanView!D5:P5),$D$4:$P$4,IF(D$3:P$3>$E$20,1,0)))*(IncludeDailyCost*$S5+IncludeTandS*$T5)*5

You already have the dates in row 3 of PlanView. If you add the values from row 3 into plan view, add the 2 “includes” names and the values in cols S&T into planview, and these formulas, all the data could be done in PlanView without any of the “intermediate calcs” on the current sheet and the current sheet could be eliminated.

Steve

Reply | Quote

Hi Steve

Many thanks… Now I’ll try and understand why it works 🙂

As an aside… Array formulas are not something I make great use of, due largely to lack of understanding, can you point me at any definative resources for bringing myself up to speed with them?

Regards
Peter

Reply | Quote

(Edited by WebGenii on 06-Oct-02 18:17. added Hyperlink)

Try this site by Chip Pearson:
http://www.cpearson.com/excel/array.htm%5B/url%5D

Reply | Quote

Hi Steve

Thanks… The site reference is very useful AND the array calculations, with suitable modifications do exactly what I asked for…. Of course, that isn’t what I really want to do.. My fault.. I’ve re-attached the sample spreadheet in the hope that explains what I’m aiming at.

In essence what I need to be able to do is to summarise for ALL rows in the bigger array where the Group matches that entered/selected. i.e. What I end up with is a breakdown of costs for specific Groups by financial quarters. Am I making any sense?

Regards
Peter

Reply | Quote

Peter

I looked at your sheet, and hope I’m understanding it right.

I wondered why what you’re doing is not much more simply achieved by a bit of restructuring and a Pivot table.

I run a spreadsheet with transactions and transaction dates (organised as a list). To make it produce reports similar to the ones you seem to be asking for I create a new column or two of simple of ‘tag’ fields (e.g. QUARTER, and LEDGER) which can be easily calculated off the source data.

The resulting structure can then easily be summarized as a Pivot Table. I’d assume that a data table a bit like that would work for you too.

Reply | Quote

Hi Andrew

Thanks for your feedback. You may have a good point. There is a lovely Irish expression that I am very fond of, “Sure you don’t want to be starting from here…”, which as with many things Irish isn’t daft at all, it’s a polite means of saying “@#$!%, you’re well lost mate”. Hence, you may be right about the pivot table. I’ve started down one particular alleyway and the light may be getting a bit dim..

The model I am working on is a resource scheduling and costing model. The key part is getting user input for levels of resource per day. In the background I then calculate the cost of those resources. As with many of these things there is a ‘Plan’ and and an ‘Actual’. What Steve has helped me greatly with is a means of bypassing the need to have to calculate and hold in a temporary sheet the cost per week, per resource line, since all I’m really interested is the Quarterly financial cost.

The cost calculation is related to a skills profile and is subject to a monthly inflation charge. I guess I could put all this into a table, I sort of had that anyway, but was actually trying to get rid of the detailed costs tables for Plan and Actual since other than allowing me to perform sub-totaling per quarter/cost sector, they just take up a lot of space.

A bit like array formulas, I tend to avoid pivot tables, largely based upon my ignorance of them. Maybe I’ll try getting my hands dirty at some time. Especially if this particular alley gets any dimmer!

Regards
Peter

Reply | Quote