SUMIF or SUMPRODUCT formula to SUM totals based on multiple criteria

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!

Duplicate post:

=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=$A4)*('GL TL Summer VC Report'!$G$1:$AU$1=B$2))

Given in your previous post.

Thank you John, incredibly helpful as always.

Typo (which I am sure you spotted!)

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))

Or try this ...

=SUMIFS(OFFSET('GL TL Summer VC Report'!$F$2:$F$100,,MATCH(B$2,'GL TL Summer VC Report'!$G$1:$AU$1,0)),
'GL TL Summer VC Report'!$B$2:$B$100,'GL&TL VC Report'!$A3,'GL TL Summer VC Report'!$F$2:$F$100,$B$1)

Hi guys,

I have a similar request, I am trying to do the same thing into this first sheet (Total GL TL TM Data) so that i can get data from the second sheet (duplicate) so it will tell me the corresponding amount for:
- Each position
- Each shop
- Shift
and
- Date

I keep getting "1" which isn't right :/ i'm using this formula

=SUMPRODUCT(--(ISNUMBER(Duplicate!$D$3:$AR$114))*(Duplicate!$A$3:$A$114=$A2)*(Duplicate!$C$3:$C$114='Total GL TL TM Data'!$C2)*(Duplicate!$B$3:$B$114=$B2)*(Duplicate!$D$2:$AR$2=F$1))

I attached a sample. ps i haven't filled out the info for "TM" yet so that will be 0 still.

Thanks in advance!

Check your fields .....

=SUMPRODUCT(--(ISNUMBER(Duplicate!$D$3:$AR$114))*(Duplicate!$A$3:$A$114=$A2)*(Duplicate!$C$3:$C$114='Total GL TL TM Data'!$C2)*(Duplicate!$B$3:$B$114=$B2)*(Duplicate!$D$2:$AR$2=F$1))

Checking Shift (text) vs Position (Numeric)

Yeah sorry john I'm still not seeing the problem

Sorry there's 2 "position" collumns

Fixed what you had mentioned before but still having issues. The number it is presenting is incorrect. Any help is appreciated.

Re-post file with formulae showing expected results.

okay, I attached another sample of some of the data im working with. The # in each date should be equivalent to the following:
- Same shop
- Same position (not position #)
- Same shift
and Same Date

These numbers vary, anywhere from 0 to 60ish.

i've tried to recreate the formula I was using yesterday but had no luck, the one I came up with is giving me #N/A so obviously I'm doing something wrong. Here it is below:

=SUMPRODUCT(--(ISNUMBER(Duplicate!$D$3:$AP$170))*(Duplicate!$A$3:$A$170=$A2)*(Duplicate!$B$3:$B$170='Total GL TL TM Data'!$D2)*(Duplicate!$C$3:$C$170='Total GL TL TM Data'!$C2)*(Duplicate!$D$2:$AR$2='Total GL TL TM Data'!F$1))

Try

=SUMPRODUCT((Duplicate!$D$3:$AR$170)*(Duplicate!$A$3:$A$170=$A4)*(Duplicate!$B$3:$B$170=$D4)*(Duplicate!$C$3:$C$170=$C4)*(Duplicate!$D$2:$AR$2=F$1))

The data in "Duplicate" appears to be daily, but in the "Total ...." it is "weekly" intervals. Is some sort of summation required?

Ended up just using an index match formula, thanks for the help