SUMPRODUCT formula compare then count

[Greg in CO]

Me again!

I have a variation on a problem I received the solution for from you fine
folks, and my attemtps to tweek the formula I'm using haven't been all that I
would hope.

So, here goes:

This is the same sheet from my question "To Count or not to Count" for which
I got this spiffy formula that confirms the existance of an entry in Column A
and in Column N and then counts the corresponding entry in Column N:

=SUMPRODUCT(--($A$12:$A$34<>""),--(N12:N34<>""))

What I need to add to this formula is an argument where the formula now
compares a range of position on Worksheet B (abbrev. for here WSB) for a
department, and, if there is a match, returns the count of the corresponding
positions on WSA. I looked at SUMPRODUCT where there were = arguments for
specific criteria, but I need the formula to match any criteria in a range.

On Worksheet A (abbrev. for here WSA), in Column A I have a list of
positions (Chief Chicken Plucker, Beek Tweaker, Feather Fluffer, etc.), in
Column N I have the hours for each role for January.

So, WSB is a worksheet where various information for a specific department
(Plucking and Tweaking Department) is fed from other worksheets. It has in
Column A a list of positions for that department only (Chief Chicken Plucker,
Beek Tweaker, etc.....Feather Fluffer is not part of this department). On
WSB, there are cells labled according to the months, like on WSA.

What I have tried to make the formula do is, for a count result in the
January cell on WSB, compare any entry on WSB Column A (Positions specific to
the Plucking and Tweaking Dept.) against any entry on WSA Column A (Position)
and where there is a match (versus just any entry at all), confirm there is a
corresponding entry in Column N on WSA, and then return count of
corresponding entries in Column N (Hours) on WSA.

WSA:

Column A Column N
Chief Chicken Plucker 40
Beek Tweaker 40
Feather Fluffer 10

WSB:

Column A
Chief Chicken Plucker
Beek Tweaker

Count returned in Jan cell on WSB: 2

Logic: Since, on WSA, Column A there are two entries that match entries on
WSB Column A, look in Column N on WSA, confirm there are entries
corresponding to the entries in Column A and count them.

Thanks in advance for any help...in reading the posts here, I have learned
tons!

 

[Roger Govier]

Hi Greg

Assuming that column N represents January hours, then

=SUMPRODUCT((WSB!A1$A$1:$A$100=WSA!$A1)*(WSB!N$1:N$1000<>"")=
SUMPRODCT(--(WSA!$A$1:$A$1000=WSA!$A1))

Should return True or False

 

[Greg in CO]

Hi Roger!

Thanks for the response. I tried entering the formula, but there might be a
typo or I may not have been clear on what is on which worksheet.

Actual worksheet names:

DeptDashboard
ProjectA

Data on each Worksheet:

DeptDashboard:

Column A - Positions specific to a Department - range A132:A150
Cell G13 - Location for the formula, where the count of positions with hours
in Jan on ProjectA Worksheet that match any of the positions on
DeptDashboard!A132:A150 would return.

ProjectA:

Column A - Positions, range A12:A34
Column N - Hours for Jan (O is Feb, P is Mar, etc.) - range N12:N34

In the formula you posted, was there an extra argument in the first array?:

=SUMPRODUCT((WSB!A1$A$1:$A$100=WSA!$A1)*(WSB!N$1:N$1000<>"")=
SUMPRODCT(--(WSA!$A$1:$A$1000=WSA!$A1))


I tried substituting my specific info, but got a #NAME error and Excel said
there was a parens missing.

Again, all help is appreciated!

BTW, is there a preferred format for presenting info from our Excels, as we
cannot post tables or screenshots? I'd like to make sure that what I present
is clear the first time around.

 

[Greg in CO]

All,

I may have figured one out for myself..whoohoo!

Here is the logic, followed by the formula:

"Look in Column A on SheetA and compare it with entries in Column A on Sheet
B and if there are any matches, then look in Column N on SheetA and if both
have entries and there is a match between the Column A entires on both
sheets, count the entries in Column N on SheetA which correspond to entries
in Column A on SheetA (which match the entries in Column A on Sheet B), then
place the count in a cell on Sheet B."

Formula in SheetB:

=SUMPRODUCT(--(ISNUMBER(MATCH(SheetA!$A$1:$A$10,$A$20:$A$50,0))),--(SheetA!N$1:N$10<>""))

This appears to work, but I will be doing some additional testing.

Now, after uncovering this formula (thanks to the posters here and the XL
site in the UK...lots of reading and trial and error), I needed for the
formula to do the same function across numerous ranges in the same
column(s)...I did a work around using the complete formula, then a "+" and
then a repeat of the formula with the next range replacing the original ones
on SheetA. This gives me a count for each of the ranges on SheetA, with the
total count returning in one cell on SheetB.

I am wondering if there is a shorter or more elegant way of doing it. Here
is the ugly, two-headed monster:

=SUMPRODUCT(--(ISNUMBER(MATCH(SheetA!$A$12:$A$34,$A$132:$A$160,0))),--(SheetA!N$12:N$34<>""))+SUMPRODUCT(--(ISNUMBER(MATCH(SheetA!$A$39:$A$52,$A$132:$A$160,0))),--(SheetA!N$39:N$52<>""))+SUMPRODUCT(--(ISNUMBER(MATCH(SheetA!$A$57:$A$70,$A$132:$A$160,0))),--(SheetA!N$57:N$70<>""))+SUMPRODUCT(--(ISNUMBER(MATCH(SheetA!$A$75:$A$88,$A$132:$A$160,0))),--(SheetA!N$39:N$52<>""))+SUMPRODUCT(--(ISNUMBER(MATCH(SheetA!$A$93:$A$106,$A$132:$A$160,0))),--(SheetA!N$93:N$106<>""))+SUMPRODUCT(--(ISNUMBER(MATCH(SheetA!$A$111:$A$124,$A$132:$A$160,0))),--(SheetA!N$111:N$124<>""))+SUMPRODUCT(--(ISNUMBER(MATCH(SheetA!$A$129:$A$146,$A$132:$A$160,0))),--(SheetA!N$129:N$146<>""))+SUMPRODUCT(--(ISNUMBER(MATCH(SheetA!$A$151:$A$168,$A$132:$A$160,0))),--(SheetA!N$151:N$168<>""))+SUMPRODUCT(--(ISNUMBER(MATCH(SheetA!$A$173:$A$190,$A$132:$A$160,0))),--(SheetA!N$173:N$190<>""))

Again, my thanks to all the posters here...I have learned a lot!!!!!!