Help with SUMPRODUCT formula to tally all values

**relmasri** · 05-30-2016, 02:47 PM

Hi all,

Looking for help creating a formula that will count the appropriate numbers. I need it to sum all the 1's into the specific categories based on the following criteria.
- Shop
- Shift (A - for sample attached)
- Position (Group Leader & Temporary group leader for sample attached)
- Date

First Sheet is the raw data I need to sort, second sheet is where the data needs to be sorted. Each data on the second sheet will have a number that corresponds to the sum for that specific date.

The current formula i've been trying to use is:
=SUMPRODUCT('GL TL Summer VC Report'!$B$2:$B$1635=$A3)*('GL TL Summer VC Report'!$G$1:$AU$1=B3)*('GL TL Summer VC Report'!$F$1:$F$1635='GL&TL VC Report'!$B$1)

No luck though, any help is appreciated!

**zbor** · 05-30-2016, 02:48 PM

Your post does not comply with Rule 1 of our Forum RULES. Your post title should accurately and concisely describe your problem, not your anticipated solution.

Use terms appropriate to a Google search. Poor thread titles, like Please Help, Urgent, Need Help, Formula Problem, Code Problem, and Need Advice will be addressed according to the OP's experience in the forum: If you have less than 10 posts, expect (and respond to) a request to change your thread title. If you have 10 or more posts, expect your post to be locked, so you can start a new thread with an appropriate title.

To change a Title go to your first post, click EDIT then Go Advanced and change your title, if 2 days have passed ask a moderator to do it for you.

(This thread should receive no further responses until this moderation request is fulfilled, as per Forum Rule 7)

**JohnTopley** · 05-30-2016, 04:09 PM

Try

in B3

=SUMPRODUCT(--(ISNUMBER('GL TL Summer VC Report'!$G$2:$AU$100))*('GL TL Summer VC Report'!$F$2:$F$100=$B$1)*('GL TL Summer VC Report'!$B$2:$B$100=$A3)*('GL TL Summer VC Report'!$G$1:$AU$1=B$2))

Copy across and down