Sum Cells against specific criteria in other Cells

fredericgilbert
fredericgilbert used Ask the Experts™
on
Hi,

I am looking for a formula that sums up Searches in Column B that are in between cells containing http in Column A...

Note: the numbers of cells to sum up is always different. The Webpages names are never the same.

Any help would be appreciated.
Thanks in advance
20120416-SumCellsWithCriteriaInO.xlsx
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Try this ARRAY formula in C2

=IF(B2="",SUM(OFFSET(B2,1,0,MATCH(0,--B3:B42,0))),"")

To enter an array formula:
enter the formula in the cell
select the cell
press F2
press shift-ctrl-enter

Author

Commented:
Hi ssaqibh,

Thanks for the proposed solution but it covers only partially the situation.
Most certainly, my request and the associted excel was too simplified and not explicit enough.

The sum result should be in same column as the values to sum up, just above them as a total and the criteria triggering the sum up is the presence or not of "http" in column A.

There is 2 parts to the formula I am looking for:

1. when http is detected, it sums up the Monthly Searches' cells below.
>>This is the part that I looking or help.

2. when http is not detected, the formula is getting the Monthly Searches from in a pivot elsewhere in the workbook (I have that part ok with the getpivotdata)...

Question: can this be done in the same column without helper column?

Thanks
Then try this:

ARRAY-enter this revised formula in B2
=IF(LEFT(A2,5)="http:",SUM(OFFSET(B2,1,0,MATCH(0,--B3:B42,0))),"")
Select B2
Ctrl-c  (copy)
Select B3 down to the end of the data
Press F5
Click Special
Select Blanks
Ok
Ctrl-V (Paste)
2. when http is not detected.....

Can you show me how?

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial