Up all night.. this better not be an easy answer!

P

phive_

New Member
Joined
Mar 3, 2005
Messages
38
Hey gang,
I’ve been up all night trying to figure out how to do this and I'm completely stuck. What I'm trying to do is have a "Summary" page so I can have a sort of 'top sheet' from data I’ve dumped from my ms database.

I have three columns on this particular sheet (we'll call it "RawData").
COL A: FortheWeekOf – Date of the last day of that week
COL B: AnaystsName – Analysts that handled the particular problem
COL C: CountOfID – Number Times that week that analyst handled a problem (total)

Example:
Fortheweekof | AnalystName | CountOfID |
John Doe | 05/06/2006 | 17 |
Bob Smith | 05/13/2006 | 24 |
Bob Smith | 05/20/2006 | 29
Bob Smith | 05/06/2006 | 15 |
John Doe | 05/13/2006 | 27 |
John Doe | 05/20/2006 | 19 |

On my main sheet, what I’m TRYING to do (quite unsuccessfully I made add) is provide a summary from this data based on the week.
1. ROW2: Week (date) - Will be the same as FortheWeekOf
2. COL A: Analysts Name
3. COL B - ?: Count of problems for the analysts under the specific date

Example:
helpme.jpg


The problem is that I can't do a COUNTA or COUNTIF (I don't think anyway) because depending on the week, the analyst might have been off and might not even be in my RawData. Secondly, besides the data being in the same column they could be anywhere within that column. I COULD sort the column, however, I don't think this is going to help any. (one way the analysts are out of order, the next way the dates are out of order)

I am a intermediate VBA user so if that can help me here I'm willing to try.

Any feedback would be much appreciated!

I am off to bed now, haven't slept all night trying to figure this out.

- Brian (AKA Phive_) :roll:[/img]
 

Excel Facts

Excel motto
Not everything I do at work revolves around Excel. Only the fun parts.
Sort by date Sort by votes
Hi,

Is not a pivot table exactly what you need?
Book1
ABCDEFGHI
1FortheweekofAnalystNameCountOfIDSum of CountOfIDAnalystName
2John Doe2006-05-0617Fortheweekof2006-05-062006-05-132006-05-20
3Bob Smith2006-05-1324Bob Smith152429
4Bob Smith2006-05-2029John Doe172719
5Bob Smith2006-05-0615
6John Doe2006-05-1327
7John Doe2006-05-2019
8
Sheet1
 
Upvote 0
or try this formula approach.

Formula in F3, copied down and to the right, is: =SUMPRODUCT(--($A$2:$A$7=F$2)*--($B$2:$B$7=$E3)*($C$2:$C$7))
Book1
ABCDEFGH
1FortheweekofAnalystNameCountOfID
25/6/2006John Doe175/6/20065/13/20065/20/2006
35/13/2006Bob Smith24John Doe172719
45/20/2006Bob Smith29Bob Smith152429
55/6/2006Bob Smith15
65/13/2006John Doe27
75/20/2006John Doe19
Sheet1
 
Upvote 0
or try this formula approach.

Formula in F3, copied down and to the right, is: =SUMPRODUCT(--($A$2:$A$7=F$2)*--($B$2:$B$7=$E3)*($C$2:$C$7))

...
Click to expand...

Just a small note on syntax... It's either:

=SUMPRODUCT(--($A$2:$A$7=F$2),--($B$2:$B$7=$E3),$C$2:$C$7)

or:

=SUMPRODUCT(($A$2:$A$7=F$2)*($B$2:$B$7=$E3),$C$2:$C$7)

or:

=SUMPRODUCT(($A$2:$A$7=F$2)*($B$2:$B$7=$E3)*($C$2:$C$7))

The latter is the least desirable...
 
Upvote 0
Hi, Aladin. I'm confused about when to use the -- and when not to.

Also, when to use the * and when to use the , as the separators.

Could you possibly clarify?

Thanks
 
Upvote 0
Hi, Aladin. I'm confused about when to use the -- and when not to.

Also, when to use the * and when to use the , as the separators.

Could you possibly clarify?

Thanks
Click to expand...

Barry,

It's not that confusing at all...

Syntax SumProduct:

SUMPRODUCT(Term1,Term2,...)

Whenever a Term is a conditional like in:

($A$2:$A$7=F$2)

whose evaluation yields an array of TRUE and/or FALSE values, we need to coerce the term into an array of 1 and/or 0 values.

Whenever a Term consists of numeric values (thus a range with numbers or an array of numbers), no coercion is needed for that term.

Thus, when a Term in SumProduct is a conditional, it needs to be coerced.

There are lots of coercers to choose from - See:

http://www.mrexcel.com/board2/viewtopic.php?t=133602

When you opt for "*", you literally have to replace syntactically required comma's at least between the conditional terms. Thus, for example:

SUMPRODUCT((Term1=Cond1)*(Term2=Cond2)) [Counting]
SUMPRODUCT((Term1=Cond1)*(Term2=Cond2),Term3) [Summing]

When you opt for keeping the syntactically required comma's, you have to choose an explicit coercer for conditional terms. Thus:

With -- as coercer...

SUMPRODUCT(--(Term1=Cond1),--(Term2=Cond2)) [Counting]
SUMPRODUCT(--(Term1=Cond1),--(Term2=Cond2),Term3) [Summing]

With +0 (i.e., the addition operator) as coercer...

SUMPRODUCT((Term1=Cond1)+0,(Term2=Cond2)+0) [Counting]
SUMPRODUCT((Term1=Cond1)+0,(Term2=Cond2)+0,Term3) [Summing]
 
Upvote 0
Thanks, Aladin. That's exactly what I was looking for; a logical explanation of what to do and when to do it. Let me see if I can nutshell it and explain it back correctly:

1. A term consisting of numerical values that will be summed does not need a coercer.

ex: =SUMPRODUCT(A1:A100,.....), where A1:A100 contains numbers

2. If you have conditional terms (or a numerical term such as a range of dates) you can either:

a. use a coercer (-- in front or +0 at the end) to force them to either a TRUE or FALSE

ex: =SUMPRODUCT(--(A1:A100="Barry),(B1:B100="07/01/2006")+0,....)

b. or replace the , between the conditional terms with *, essentially combining them into one larger term.

ex: =SUMPRODUCT((A1:A100="Barry")*(B1:B100="07/01/2006"),....)

3. Conditional terms can only be counted. To sum, at least one term must be a numerical and non-conditional

Have I gotten this through my thick skull correctly?.
 
Upvote 0
Thanks, Aladin. That's exactly what I was looking for; a logical explanation of what to do and when to do it.
Click to expand...

Thanks for the feedback.

Let me see if I can nutshell it and explain it back correctly:

1. A term consisting of numerical values that will be summed does not need a coercer.

ex: =SUMPRODUCT(A1:A100,.....), where A1:A100 contains numbers
Click to expand...

Right.

2. If you have conditional terms (or a numerical term such as a range of dates) you can either:

a. use a coercer (-- in front or +0 at the end) to force them to either a TRUE or FALSE

ex: =SUMPRODUCT(--(A1:A100="Barry),(B1:B100="07/01/2006")+0,....)
Click to expand...

Almost...

=SUMPRODUCT(--(A1:A100="Barry""),--(B1:B100="07/01/2006"+0),....)

for the =("07/01/2006")+0 is a separate issue. Here you need to coerce the text date into a true date. Double quotes around the date makes it a text date, hence +0 to cerce the date. The term

(B1:B100="07/01/2006"+0)

needs -- because you test B1:B100 for being equal to a coerced date.

b. or replace the , between the conditional terms with *, essentially combining them into one larger term.

ex: =SUMPRODUCT((A1:A100="Barry")*(B1:B100="07/01/2006"),....)
Click to expand...

The text date still needs to be coerced into a true date...

=SUMPRODUCT((A1:A100="Barry")*(B1:B100="07/01/2006"+0),....)

3. Conditional terms can only be counted. To sum, at least one term must be a numerical and non-conditional...
Click to expand...

Yeah. When there is no range to sum, only conditional terms, we will be doing a multiconditional count.
 
Upvote 0
Few!

Thank you all for your comments and feedback. Needless to say I’m back on the right track thanks to you.

And not only did you fix my problem, I learned a thing or two along the way!

Thank you all!

:-D
 
Upvote 0
You must log in or register to reply here.

Similar threads

E
  • Question Question
Rolling Count
Replies
1
Views
481
awoohaw
awoohaw
B
  • Solved
Dynamic Filter by row
Replies
5
Views
746
BrettJH
B
O
  • Solved
Helpful formatting/delimeters for a multi-use text box?
Replies
4
Views
242
Office_Drone
O
C
  • Solved
FILTER function to return ONLY records with most recent date
Replies
6
Views
5K
Fluff
Fluff
S
  • Question Question
Deleting multiple rows, columns, unmerging, pivot table all at once.
Replies
2
Views
125
HighAndWilder
H

Forum statistics

Threads
1,226,216
Messages
6,189,675
Members
453,563
Latest member
Aswathimsanil

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top