How to sum particular cells given repeating sets of rows and columns?

[excel620]

Is it ok if i post a link to a google spreadsheet?

This is really hard to describe and a screenshot won't be enough to explain this

https://docs.google.com/spreadsheets/d/1o5T5e0TDu7x733nnUwQhc2_Fo8-NMIIXTnxWMYebVaA/edit?usp=sharing

Basically, at the bottom I want to sum all of these like how line 134 is summing. (I had them all summing but putting it in google docs lost the formulas). I've uploaded an excel too just in case

Is there a way to sum this without manually going through and selecting which cells you want to sum? I can't change anything on top (esp can't move around anything in yellow, the N/A has to stay)

For example, I want F134 to sum up any Data 1 entries for Jan which are also Company A (customer) Check (work type).

I've tried sumifs with an index, and I'm looking at sumproduct, etc. But I just can't figure out a way to do this.

Any help? Thanks!

 

[Hui]

In F134 you have an answer 16
which is F8 & F21

Why aren't F83 and F95 included?

If they should be included
In
Column E134 Change to Data 1 etc below it
Then in F134 : =SUMPRODUCT((IF(ISNUMBER(F$8:F$127),F$8:F$127,))*(F$5:F$124=$D$134)*($E$8:$E$127=$E134)) Ctrl+Shift+Enter

 

[bosco_yip]

Try.........

In F134, formula copy across and down :

=SUMPRODUCT((F$5:F$124=LOOKUP("zzz",$D$134:$D134))*(F$7:F$126=MID($E134,8,LEN($E134)-8))*(OFFSET($E$5,MATCH(LEFT($E134,6),$E$6:$E$16,0),,120)=LEFT($E134,6)),OFFSET(F$5,MATCH(LEFT($E134,6),$E$6:$E$16,0),,120))

and, select range F134:O145 >> Custom Cells Format, in the Type box enter: 0;-0;;

Regards
Bosco

 

[excel620]

Hi thank you guys for both formulas, I'm trying to figure them both out

Bosco, what does 0;-0;; do? Is it necessary?

EDIT: Bosco I'm having a hard time understanding your formula (for example why is the look looking at "zzz"). Do you think you could explain what it does in words?

Thank you!!!

 

[bosco_yip]

Hi thank you guys for both formulas, I'm trying to figure them both out

Bosco, what does 0;-0;; do? Is it necessary?

EDIT: Bosco I'm having a hard time understanding your formula (for example why is the look looking at "zzz"). Do you think you could explain what it does in words?

Thank you!!!

Hi,

1] Custom Cells Format: 0;-0;; -->> to remove 0

2] "zzz" is the Biggest Text Value in the below Lookup formula, to return the last text value in the range.

you can put this formula in Q134, and copy down to check the results:

=LOOKUP("zzz",$D$134:$D134))

Regards
Bosco

 

[excel620]

Thanks for that Bosco. I understand that part now.

Before I look at the rest of the formula, I'm having trouble understanding how sumproduct works in a more basic sense.

Could one of you help me here? Hui's formula

=SUMPRODUCT((IF(ISNUMBER(F$8:F$127),F$8:F$127,))*(F$5:F$124=$D$134)*($E$8:$E$127=$E134))

works fine as he said, in F134. I of course have to change E134 to say "data 1" for it to work. Here are my questions:

1.) If I change E134 to 'data 2' why doesn't the formula work anymore?

2.) I tried to duplicate this sumproduct on another example sheet and I can't get it to work. One thing I don't understand is why both of your formulas have ranges that aren't the same. One range goes as far as row 124, while others go all the way down to 127.

Thanks

 

[bines53]

Hi,

Maybe, there is another solution,Which version of Excel you have ?

David

 

[NARAYANK991]

Hi ,

See the attached file.

Narayan

 

[bines53]

Hi ,

The formula is longer, but without volatile function.
excel 2010 and above,

=SUMPRODUCT(AGGREGATE(15,6,F$8:F$127/((ROW($A$1:$A$120)<=(AGGREGATE(15,6,ROW($E$8:$E$127)-7/((F$5:F$124=LOOKUP("zzz",$D$134:$D134))*(F$7:F$126=MID($E134,8,LEN($E134)-8))=1),2)+8))*(ROW($A$1:$A$120)>=MATCH(1,(F$5:F$124=LOOKUP("zzz",$D$134:$D134))*(F$7:F$126=MID($E134,8,LEN($E134)-8)),0))*(MID($E134,1,6)=$E$8:$E$127)=1),{1,2}))

David