SUMIF on specific cells

[JimM]

I'm sure this should be simple but brain's not working today!

I want to add together specific individual cells based on the value in other cells eg. add together A1 + D1 + G1 but only if the cells B3, E3 and H3 have the word 'Act' in them
It's not an all or nothing position so result could be A1 + G1 if only B3 and H3 contain 'Act'

The actual formula would have to cover more than 3 cells

Hopefully that makes sense and any pointers would be appreciated

 

[gaz_chops]

Try

=SUMIFS($A$1:$G$1,$B$3:$H$3,"ACT")

basically just offset the Criteria Range by 1 col

 

[Eric W]

Consider:

Book1
	A	B	C	D	E	F	G	H
1	1			2			3
2
3		act			act			act
4
5	6
6	6
Sheet1

Cell Formulas
Range	Formula
A5	A5	=SUMIF(B3:H3,"*act*",A1:G1)
A6	A6	=SUMPRODUCT(A1:G1,--ISNUMBER(SEARCH("act",B3:H3)),--(MOD(COLUMN(A1:G1),3)=1))

If the cells between "act" on row three will never have "act" in them, then the A5 formula will work. If you need to ignore those cells, the A6 formula will work. Both these formulas assume a constant distance between cells. If you have differing offsets, there are more complicated ways to do it.

 

[gaz_chops]

Note - keep the sum range & the criteria ranges the same number of columns.

 

[JimM]

Thanks guys, never knew you could offset the ranges!

Every day's a school day

 

[gaz_chops]

You're welcome and yep that's why I love this forum