Am I being too ambitious?

[Flick]

I'm putting together a stock summary of all the desks in the office and any spares, but I'm trying to work a function out on a test table and not really getting close; I'm also a bit new to excel. Here's a simple example of the type of thing I'm working with:

I want this function to look across the header row, look at the department (let's choose "Call Centre") and return a total of how many desks have a phone (where "x" means yes (this can be changed if it helps)).

Knowing I'll probably chop and change the order of items, it would make more sense if it were to look for "Phone" in the header rather than write a function to search in a specific range of cells.

I'm not sure if I'm being too ambitious with this idea, but any help would be grateful.

 

[Mike H..]

I'm putting together a stock summary of all the desks in the office and any spares, but I'm trying to work a function out on a test table and not really getting close; I'm also a bit new to excel. Here's a simple example of the type of thing I'm working with:

I want this function to look across the header row, look at the department (let's choose "Call Centre") and return a total of how many desks have a phone (where "x" means yes (this can be changed if it helps)).

Knowing I'll probably chop and change the order of items, it would make more sense if it were to look for "Phone" in the header rather than write a function to search in a specific range of cells.

I'm not sure if I'm being too ambitious with this idea, but any help would be grateful.

Hi,

I think you're making awful hard work of it wanting to do it that way but here we go. I have assumed that the Department will be a fixed column and have used column B. Try this

=SUMPRODUCT((B2:B101=K1)*(OFFSET(INDIRECT(ADDRESS(1,MATCH(L1,A1:H1,0))),1,0,100,1)="X"))

Where K1 is the Department you want to check and L1 contains Phone

 

[Somendra Misra]

Hi Flick,

I don't know exactly what format you want your result, but see the attached file for the solution through Pivot table.

Regards,

 

[Flick]

Hi,

I think you're making awful hard work of it wanting to do it that way but here we go. I have assumed that the Department will be a fixed column and have used column B. Try this

=SUMPRODUCT((B2:B101=K1)*(OFFSET(INDIRECT(ADDRESS(1,MATCH(L1,A1:H1,0))),1,0,100,1)="X"))

Where K1 is the Department you want to check and L1 contains Phone

Thanks Mike, this works great.

One little query though: I'm thinking of creating a summary page so all these x's aren't seen, so where the ranges B2:B101 and A1:H1 are I've tried linking to another sheet (so now showing as 'Summary'!B2:B101) but this only returns 0 - would this formula work like that, or am I pushing my luck?

And yes, hard work for something fairly menial but I wanted it to be almost self-correcting in a sense should I move headings around; I'm lazy!

 

[Flick]

Hi Flick,

I don't know exactly what format you want your result, but see the attached file for the solution through Pivot table.

Regards,

Hi Somendra,

I can't say I know anything about Pivot Tables but I was looking at something along those lines - I will have to look in to them a bit more!

 

[Mike H..]

Thanks Mike, this works great.

One little query though: I'm thinking of creating a summary page so all these x's aren't seen, so where the ranges B2:B101 and A1:H1 are I've tried linking to another sheet (so now showing as 'Summary'!B2:B101) but this only returns 0 - would this formula work like that, or am I pushing my luck?

And yes, hard work for something fairly menial but I wanted it to be almost self-correcting in a sense should I move headings around; I'm lazy!

Hi,

Here's the formula modified to work from another sheet.

 

[jeffreyweir]

Flick - PivotTables are the most powerful thing in Excel. They are perfect for what you are trying to achieve above, and for a whole lot more besides.
Here' some draft content from the book I'm working on - Excel for Superheroes and Evil Geniuses - that discusses this:

What exactly is a Pivottable? There’s several schools of thought on this:

1. A PivotTable is an interactive tool that lets you quickly and easily summarize, slice, dice, and compare large amounts of data. (Not just sales data either, but any type of data. See https://exceljet.net/blog/pivot-tables-you-havent-seen-before for a great example of this)

2. A PivotTable is your ticket to impressing the boss. (See https://exceljet.net/blog/can-pivot-tables-save-your-job-maybe for a great story on that, too)

3. A PivotTable is a way to avoid writing really complicated formulas that you won’t understand in a week’s time, or a way to avoid writing complicated VBA code.

All answers are correct. With permission of my new friend Dave from www.ExcelJet.com I’ve provided links above that should really ‘sell’ the concept of using PivotTables for those first points, because Dave sums it up much more eloquently that I could here, and the focus of this part of the book is really on that last point – PivotTables are often a sure-fire way to reduce the complexity of your spreadsheet. So much so, that whenever you are working out how to tackle a problem, you should ask yourself Will a PivotTable help with this?

 

[Ajesh]

a bit diffrent solution. Very basic in nature. can be made dynamic with some tweeking.

See if this helps.

Thanks/Ajesh