SUMIF Range Query

[WeeJudz]

Hi,

I'm looking to carry out a SUMIF Function, however I don't want to use the regular sum_range - I want to add certain individual cells instead. This seems simple but I've not had any joy - it's along the lines of below. I'm using Excel 2007.

=SUMIF(A4:A11,"H",B4+B7+B8+B10) however this sum 'range' part does not work.

Thanks in advance,

Judith

 

[Matt Rogers]

Sorry, there was an error ...

 

[Gerald Higgins]

Hi, you're always going to have problems if your Sum range is not of a similar size to your criteria range.

What's wrong with doing
=sumif(a4:a11,"H",b4:b11) ?

Or maybe even
=sumif(a4:a11,"H",b4:b11)-b5-b6-b9-b11 ?

 

[WeeJudz]

Hi, sorry should have noted this - reason I can't use the range is because it's specific totals (cells) I want to sum. If I use the range (B4:B10) it'll also add the data as well as totals, some or which is not required. It's purely the totals so has to be specific cells.

 

[Matt Rogers]

One more try ...:
=IF(A4:A11="H",B4+B7+B8+B10,"")

Alternative:
=IF(AND(A4="H",A5="H",A6="H",A7="H",A8="H",A9="H",A10="H",A11="H"),B4+B7+B8+B10,"empty")

 

[WeeJudz]

Great, I'll try this at work tomorrow.

Alternatively, the other way would be to count the data instead of totals. It'd be a COUNTIFS, first criteria the "H" in column A, and second would be the criteria in the data column B. Is there an easier way to count numerous criteria in a COUNTIFS function (ie =COUNTIFS(A4:A11,"H",B4:11,"O"+"A"+"DS"+"NS") ?

 

[Matt Rogers]

Ok,
Sorry, it's Thanksgiving Day ...
CU tomorrow.

Note:
In the first formula a single "H" is sufficient.
In the second formula "H" must be everywhere.

 

[Anand Sharma]

Hi,

I guess you want only few of cells in sum range, if yes then

Try using =SUMIF(A4:A11,"H",B4,B7,B8,B10) instead =SUMIF(A4:A11,"H",B4+B7+B8+B10)

 

[live_excel]

Hi,

Please use this formula.Hope it will work

=SUMIFS(A4:A11,B4,"H"&E2,B7,"H"&E3).

 

[Tetra201]

For the problem from Post #1, try:

=SUMIF(A4:A11,"H",INDIRECT({"B4","B7","B8","B10"}))

For the problem from Post #6, try:

=SUM(COUNTIFS(A4:A11,"H",B4:B11,{"O","A","DS","NS"}))