# Posts by Robert Mika

• ## Indirect formula to return text value

Re: Indirect formula to return text value

File size 278b?
Mb?ok kb?

• ## Indirect formula to return text value

Re: Indirect formula to return text value

This formula is used to deliver a summary from sheets that you specyfied in

so what if you had more than one value to return?
SUMPRODUCT itself is used to summarized not to index data.
You need copletly different formula.
The forum may not allow to post your workbook if you had it in xlmb format.
But this is just my guess.

• ## Average only the last X numbers in a column (basing on criteria)

Re: Average only the last X numbers in a column (basing on criteria)

=AVERAGE(SUBTOTAL(9,OFFSET(B3,LARGE(IF(A3:A16="A",ROW(A3:A16)-ROW(B3)+1),{1,2,3}),0,1)))
or for N values
=AVERAGE(SUBTOTAL(9,OFFSET(B3,LARGE(IF(A3:A16="A",ROW(A3:A16)-ROW(A3)+1),ROW(INDIRECT("A1:A"&C1))),0,1)))
where C1 is the number of largest

• ## excel formula help

Re: excel formula help

Attached a workbook to see how your data are being set up.

• ## excel formula help

Re: excel formula help

You used whole columns ranges- try to minimized as possible.

• ## excel formula help

Re: excel formula help

=SUMPRODUCT(COUNTIFS(INDIRECT("'"&F2:F6&"'!a1:a1000"),"c",INDIRECT("'"&F2:F6&"'!b1:b1000"),"no"))
Where F2:F4 is your list of Sheets
Sheet1
Sheet2
.
.
.

• ## sum if no blanks in matching cells

Re: sum if no blanks in matching cells

To post a workbook go into advance option (below the table where you are writting) and then you have an icon to attached your workbook.

• ## sum if no blanks in matching cells

Re: sum if no blanks in matching cells

Can you post a workbook?
Column C in this case has values and the result is as expected.
Are your cell literlally "BLANK" or just empty?

• ## sum if no blanks in matching cells

Re: sum if no blanks in matching cells

=if(sumproduct(--(isnumber(d2:d4)))=rows(d2:d4),sum(d2:d4),"")

• ## Insert subtotal with more than one change

Re: Insert subtotal with more than one change

Quote from Rockymtngirl;683517

Thank you very much. I have used Countif before and once I studied the way you put the formula together, it is very simple, makes sense, and works great. Many, many thanks.

You are welcome.

• ## match cells in excel with all characters same except one

Re: match cells in excel with all characters same except one

You are welcome.

• ## Lookup first value backwords

Re: Lookup first value backwords

Yes you ae right NBVC I did not incorporate (did not see) the second condition.

• ## Lookup first value backwords

Re: Lookup first value backwords

Quote from Armando Montes;683151

One way:
=LOOKUP(2,1/(A1:A8=3),B1:B8)

This is not about last (max) for 3 is for 3-1.

• ## Lookup first value backwords

Re: Lookup first value backwords

This will give you what yiu need:
=MAX(IF(A5:A12=3-1,B5:B12))
Confirm COntrol+Shift+Enter
(you can repalce 3 with let say D1)
But your numbers must be in ascending order

• ## Totalling numbers by names - how?

Re: Totalling numbers by names - how?

forum.ozgrid.com/index.php?attachment/56203/