VLOOKUP & sum over multiple sections

[julievandermeulen]

I have a worksheet that i would like a formula for. I'm having trouble figuring out the correct formula and maybe it won't even work.

In cells AA11:AA40 I would like the sum form H11:S38 depending on the text in B11:B38 & E11:E38. For example B11 has "Loops" and E11 has "Albion" so I want the sum of (H11:R11) to be entered in cell AA11. The sum of H13:R13 to be enter in column AA depending on Text in B13 & E13.

But is it possible to also have it sum from H57:R84 (up tp 30 pages) or would each page need to be calculated separately?

Bucket Punch Sheet.xlsx

1drv.ms

 

[Cubist]

Which version of Excel are you using? Are you still using XL 2013?

 

[julievandermeulen]

Microsoft 365 MSO

 

[Cubist]

In cell AA10, try:

=SUM(FILTER(H:S,(B:B=W10)*(E:E=X10)))

 

[julievandermeulen]

That works for B11:S38 can I also have it calculate B57:S84 (page 2) and so on up to 30 pages? Do I need to set that up for each page?

 

[Cubist]

It's looking at all the pages. It's going to give an error if the conditions are not found. You can add an error handler like this.

=SUM(FILTER(H:S,(B:B=W10)*(E:E=X10)),"not found")

 

[julievandermeulen]

that didn't work I get value error with that.

 

[Cubist]

Forgot about the SUM, you can return 0.

=SUM(FILTER(H:S,(B:B=W10)*(E:E=X10),0))

 

[julievandermeulen]

Awesome!! that works thank you. I had to add a few things and reformat. I have a couple more questions.

is there a way to sum columns N7:AC32, based on if there is time entered in column B and E. For example, in column AK, I would like the sum of N7:AC12, based off the time in B7:E7, and the number in column AG. Then in Row 13 because there is times in B13:E13 again the sum starts over and is separate for that row?

Also is it possible to do a SUMIF formula based on if the check box in L36 is checked?

Bucket Punch Sheet (1).xlsx

1drv.ms

 

[Cubist]

The merged cells are problematic. Is there a reason to use merged cells instead of making the height of the rows taller (same with the columns)?