Need Excel Advanced Formula Help 1

[caveman75]

Ok, I have a new one and a real hard one!!

I was asked to combine 3 sheets into 1. I'm going to explain what I need help with in detail so that you understand what I need done.

I have labeled Column C as "Supervisor Name", Column H as "Reason" and Column N as "# of times".

Column C is going to store different Supervisor names.
Column H will store different reasons.
Column M will show the Supervisor Name next to times on hold.
Column N will show and count the number of times someone was on a specific reason.

The trick will be Column N because I need it to count the amount of times a specific Supervisor had a specific reason.

Example:
Col C Col H Col M Col N
Name State S.Name #times On Hold
Mr Tek On Hold Mr Tek 3
Ms Help Idle Ms Help
Mr Post On Hold Mr Post 1
Mr Tek On Hold
Ms Help Idle
Mr Tek On Hold

Column C will show the Supervisor name several times whereas Column M will only show their names once because Column M is only meant to show how many times a Supervisor was on hold.

 

[macropod]

Hi caveman75,

For your posted data, try the following array formula in N2:
=SUM(IF(($C$2:$C$7=$M2)*($H2:$H7=N$1),1,))
and copy down to N4.

One change will also be required to the Col N heading. Instead of '#times On Hold' use 'On Hold'. This will work well if Column, O has an 'Idle' heading. If so, copying the formula across to that column will give you the 'Idle' counts. Alternatively, change 'N$1' in the formula to 'On Hold'.

Cheers

[MS MVP - Word]

 

[caveman75]

I may need to e-mail whomever is nice enough to help me with what I'm trying to do. It is advanced so its very difficult for me to explain without something getting lost in translation. So who is up for the challenge?

 

[caveman75]

My spread sheet extends from Column A to Column P. Not all of the columns are applicable I am giving a rough idea of where everything is. The only columns that are applicable are Column C7 (Supervisors), Column G7 (On Hold) & then I have Column M7 (Supervisor Tally) & Column N7 (On Hold Tally). When I place a supervisor name in Column C8 and the term "on hold" under Column G8, then I need Column N7 (On Hold Tally) to count the on hold that I placed in Column G8. I need Column M7 (Supervisor Tally) to = Col C7 (Supervisor). It sort of looks like this:

Col C Col G Col M Col N
7 Supe.(Title) On Hold(Title) Sup.Tally(Title) O.H.(Tally)
8 Mr Bright On Hold Mr Bright 3
9 Ms Mason Idle Ms Mason 0
10 Mr Richard Idle Mr Richard 0
11 Mr Bright On Hold
12 Mr Bright On Hold

 

[mintjulep]

It mostly sounds like you need a pivot table.

 

[macropod]

Hi caveman75,

Did you try using the array formula I posted, adjusted to suit your actual ranges?

As far as I can tell from all you've posted so far, that formula does exactly what you asked for, except that you've now changed the column H references to Column G, and the row references have become 8:12, for which the array formula in N8 becomes:
=SUM(IF(($C$8:$C$12=$M8)*($G8:$G12="On Hold"),1,))
and you simply copy this down to N10.

Note the emphasis on the being an array formula. These are confirmed via <Ctrl>-<Shift>-<Enter>, instead of <Enter>.

BTW, C7 G7, M7 & N7 are cell addresses, not column references.

Cheers

[MS MVP - Word]

 

[caveman75]

thank you for replying. when i tried your formula in cell address N8 it gave me an error. when i clicked the option to see what the error was, the system told me the formula refers to an empty cell. also when i tried to add a supervisor name anywhere between cells c8 & c55 and on hold between cells g8 & g55 then none of the cells between n8 - n17 are counting the number of on holds. i just receive the forementioned error message.

 

[SkipVought]

Hi,

I like Named Ranges. I used your column heading as the names, using Insert/Name/Create - Create Names in TOP row.

Here's the formula for #times On Hold...
[tt]
=SUMPRODUCT((Name=C2)*(State="On Hold"))
[/tt]


Skip,

_[red][/red]

 

[macropod]

Hi caveman75,

The array formula I posted works with the data and cell ranges you posted. As mentioned, you need to adjust it to suit your actual ranges. Since your data span rows 8-55, you need to change the '12's in the formula to 55 and copy the formula down to N17.

Skip's suggestion to use named ranges is also worth following up. You could, for example assign:
. 'Name' to C8:C55
. 'State' to G8:G55then code the formula as:
=SUM(IF((Name=$M8)*(State="On Hold"),1,))
or you could use Skip's SUMPRODUCT formula, coded as:
=SUMPRODUCT((Name=$M8)*(State="On Hold"))

The advantages of using named ranges include being easier to read/understand and being easier to update (by simply changing the ranges named).

Cheers



[MS MVP - Word]