# SUMIFS with vertical criteria and horizontal criteria (horiz. crit. is a date range check)

1. ## SUMIFS with vertical criteria and horizontal criteria (horiz. crit. is a date range check)

Hi All,

With reference to the attached cashflow (still in it's embryonic stages at the minute - trying to modify an extremely complicated version I put together a few months ago in a way that makes it much more ergonomic and user friendly).

Tab 'MASTER' will be where I keep the detail of all expenses, frequency of payment, cost etc.
Tab 'SUMMARY' attempts to condense this information so the reader (Head Office) can have a clearer, more simplistic view over where the cash is being spent (they can always refer to 'MASTER' should they require more detail).

In cell D8 of the 'SUMMARY' tab, I want to create a formula that sums the total cost of expenses using the following rules (I will use column D and row 8 as an example but the rules can be extrapolated to the rows and columns below and to the right respectively)
- cell D8 must sum those expenses on the 'MASTER' spreadsheet who belong to the 'Staff Costs' group
- of those expenses, cell D8 must only sum those which are expected to crystallise in that given week (the given week shows on row 7 of the 'SUMMARY' tab and ends the day before the date on row 7 in the adjacent column. The date that the expenses are expected to crystallise are given on the 'MASTER' spreadsheet in columns X - NY
- as such, cell D8 on the 'SUMMARY' tab should currently show a total of \$600 because there are two expenses (\$350 and \$250 respectively) that are classified as 'Staff Costs' on the 'MASTER' spreadsheet, which are expected to be paid on or after 01 January 2016 but before 08/01/2016.

PS I'm due to catch a flight within 40 minutes so if someone could assist before then, I'll have something to do for the next 7 hours

EDIT: Apologies, had to change the attachment. The previous one contained specifics. It seems I uploaded the wrong one

2. ## Re: SUMIFS with vertical criteria and horizontal criteria (horiz. crit. is a date range ch

Hi,

Give this a try:

``Please Login or Register  to view this content.``

Cheers

3. ## Re: SUMIFS with vertical criteria and horizontal criteria (horiz. crit. is a date range ch

Thanks Southward but it doesn't seem to be working. I copied and pasted into cell D8 on 'Summary' tab but the result is still zero. Any reason for this?

4. ## Re: SUMIFS with vertical criteria and horizontal criteria (horiz. crit. is a date range ch

Hi,

You will need to remove your existing formulas first as they are causing a circular reference. Then enter the following formula into D8 and copy as necessary

=SUMPRODUCT((MASTER!\$X\$13:\$NY\$13>=D7)*(MASTER!\$X\$13:\$NY\$13<E7)*(MASTER!\$W\$14:\$W\$15=\$C8)*MASTER!\$X\$14:\$NY\$15)

5. ## Re: SUMIFS with vertical criteria and horizontal criteria (horiz. crit. is a date range ch

Hi xlnitwit,

Thanks for the solution, it was working perfectly until I modified another part of my spreadsheet. Now the formula is calculating a value error.

With reference to the 'MASTER' tab on the attached document, all expenses are displayed day by day from columns R - NS. I want to summarise the expenses on a weekly basis and as such I created the 'Cashflow_Forecast' tab. With specific reference to rows 29 and 30, your formula (or a modification of it) has been inserted in these rows to do just this. You can see that the formula is currently generating a value error. This isn't because your formula is incorrect, it is because of the way the formulae on the 'MASTER' tab in columns R - NS are structured.

If we replace the formulae in R6 & R7 to a numeric value e.g. 1 and drag across to column NS, then we see that your formulae on the weekly summary tab (that were previously generating a value error), are actually generating the correct result now, hence it definitely is the formula in R6 : NS7 that is the problem. I have identified the specific problem with these formulae too; the very last part of the formula says that if all the preceeding parts of the formula aren't true, then generate a "" value (blank). Previously this blank value was zero (and enabled your formula to generate the correct result), but I was told that it needed to change this zero value to a blank value to enable the count function in the formula to work. So my question to you is, is there any way we can modify your formula to account for this change, so that I can regain my summary of expenses on a weekly basis on the 'Cashflow_Forecast' tab?

6. ## Re: SUMIFS with vertical criteria and horizontal criteria (horiz. crit. is a date range ch

Hi again,

You can alter the formula to
=SUMPRODUCT((MASTER!\$R\$5:\$NS\$5>=C\$2)*(MASTER!\$R\$5:\$NS\$5<D\$2)*(MASTER!\$D\$6:\$D\$87=\$B29)*IF(ISNUMBER(MASTER!\$R\$6:\$NS\$87),MASTER!\$R\$6:\$NS\$87,0))
which must be array entered using Ctl+Shift+Enter and then filled down and across.

There are currently 1 users browsing this thread. (0 members and 1 guests)

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1