Posts by Fotis1991

Re: Get the Maximum Value and its corresponding cell

Max and min functions for column C.

INDEX & MATCH functions for column B

Re: Counting, but not numbers

My poor English don't let me to understand what do you mean..!

Re: Counting, but not numbers

Try

=SUMPRODUCT(SUBTOTAL(3,OFFSET($A$1:$A$1000,ROW($A$1:$A$1000)-MIN(ROW($A$1:$A$1000)),,1))*($A$1:$A$1000<>"")/COUNTIF($A$1:$A$1000,$A$1:$A$1000&""))

Re: Counting, but not numbers

No change. Formula don't count the visible cells. Count all cells in a specific range.

Re: Counting, but not numbers

Does this ARRAY(ctrl+shift+enter) formula works for you?

=SUM(1/COUNTIF(A1:A1000,A1:A1000))

Re: How to SUM a filtered column with #N/A (values only)

I think that in Excel 2010, AGGREGATE function will do this.

Re: Formula for finding last nonblank row in column and calculating average of 20 ent

..Apologize.. There is an Edit in my previous post..

Re: Formula for finding last nonblank row in column and calculating average of 20 ent

Something like this?

=AVERAGE(OFFSET(A1, COUNTA(A:A)-20,0,20,1))

Edit: Red colored..

Re: SUMIFS in multiple possible columns (lookup column somehow)

=SUMPRODUCT((Data!$A$2:$A$23=Y_sheet!$A$1)*(Data!$B$2:$B$23=Y_sheet!B$3)*(Data!$C$2:$C$23=Y_sheet!$B$2)*(Data!$D$1:$O$1=Y_sheet!$A4)*(Data!$D$2:$O$23))

My English are not so good but i'll try to explain.

SUMPRODUCT((Data!$A$2:$A$23=Y_sheet!$A$1)----Look in range Data!$A$2:$A$23 for data that match with Y_sheet!$A$1) Then in RANGE

(Data!$B$2:$B$23=Y_sheet!B$3)----------Data!$B$2:$B$23 for data that match to Y_sheet!B$3

Then in the third range ... AND finally look to find in which column exist the data that we are looking for..

(Data!$D$1:$O$1=Y_sheet!$A4) that match with the value in Y_sheet!$A4. Then add the values that you found..in this range>>Data!$D$2:$O$23

It's important to notice that some references in the formula are apsolute and some are relatives..

I hope to helps you.

Re: Repeat the amount for number of columns with Month name starting from the deliver

If i can help, it will be my pleasure..

Re: Repeat the amount for number of columns with Month name starting from the deliver

Hi

Yes. Now i understand. So this must do the job.

Does it?

Re: Repeat the amount for number of columns with Month name starting from the deliver

If i understand well your point, then you need to create a table for your cars with rent price per month and then an INDEX& MATCH will do the job..

=INDEX($J$12:$J$13;MATCH(A5;$I$12:$I$13;0))*D5

Re: SUMIFS in multiple possible columns (lookup column somehow)

One way could be this.

In your second sheet in B4 put this. Drag down and to the next column. Same way for the others.

=SUMPRODUCT((Data!$A$2:$A$23=Y_sheet!$A$1)*(Data!$B$2:$B$23=Y_sheet!B$3)*(Data!$C$2:$C$23=Y_sheet!$B$2)*(Data!$D$1:$O$1=Y_sheet!$A4)*(Data!$D$2:$O$23))

Re: Seeking help to extract some data from a list

A suggestion using formulae.

=IF(SUMPRODUCT(($A$2:A2=A2)*(A$2:$B2=B2))=1;"YES";"")

=IFERROR(INDEX($C$2:$C$13;SMALL(IF($D$2:$D$13="YES";ROW($C$2:$C$13)-1);ROW(C1)));"")

The 2nd is ana ARRAY formula.

Does this works for you?

Re: If formula to check value isn't x but if it is a, b or c

You are welcome.

Thanks for your feedback.

You must have fat fingers! Do you?:question: ( Fotis. Not Fortis.)

Re: If formula to check value isn't x but if it is a, b or c

Quote

..[FONT=&quot]If it doesn't match a draw return 0[/FONT]

You mean B2, is a Draw.Don't you?

Something like this?

=IF(B2=C2,5,IF(LEFT(B2,1)<>RIGHT(B2,1),0,2))

B2 & C2 are formatted as Text.

Re: SUMIFS ignores duplicates spanning two columns

You are welcome.

Thanks for the feedback.

Re: SUMIFS ignores duplicates spanning two columns

And another suggestion using 1 helper column.

Re: Calculating the average of text

In D21 and copy across.

=AVERAGEIF($D$7:$K$7,D$20,$D$12:$K$12)

Is this what you mean?

Re: formula for my offices invoice template i want column K17 to K33 to add 20% VAT

Post#15???