Multi-Column Sumif

[Nightlytic]

Hello,

I'm looking at a problem where I have a table of data, months as columns up top an a few categories in rows, some repeating that I need to sum. It's best visualized by example attached (I borrowed this from the Chandoo Vlookup book, coloured in bits I added in to show why my problem is a little more complex than Chandoo's formula allows for)

Please let me know, is this possible to do in one neat formula, or am I chasing rainbows?

I want to avoid using intermediary tables, as they are creating quite a mess for me all for a simple chart.

All the best!

 

[John Jairo V]

Hi, Nightkytic!

You could try:

2. =SUMPRODUCT((B4:B20="Joseph")*(COLUMN(D3:O3)-COLUMN(D3)<5),D4:O20)

3. =SUMPRODUCT((B4:B20="Joseph")*(C4:C20="Toys")*(COLUMN(D3:O3)-COLUMN(D3)<5),D4:O20)

Blessings!

 

[Nightlytic]

Hi John,

Oh I was just playing with sumproduct, nice use of Column() there! This is exactly the rainbow I was chasing. Thank you sooooo Much!

 

[Peter Bartholomew]

This look more different than it really is. It is worked through as a series of steps creating named objects rather than being built as a formula.

The key element is the range of contiguous columns 'sales.forPeriod' that captures all the sales data for the required time period.
= INDEX(sales,0,start) : INDEX(sales,0,end)
Then 'Person?' and 'Dept?' are a couple of Boolean arrays that test the two conditions:
= (Sales_Person = Person)
= (Department=Dept).

With that level of set-up effort, the required results are given by simple, meaningful array formulas:
{= SUM( IF( Person?, sales.forPeriod ) )}
{= SUM( IF( Person?*Dept?, sales.forPeriod ) )}

p.s Whilst my solutions tend to look very different from the standard approach, they are boringly similar to one another.