# Search:

Type: Posts; User: Marcelo Branco; Keyword(s):

Page 1 of 6 1

1. ## Thread: Unique value based on multiple column

by Marcelo Branco
Replies
4
Views
173

Try

A
B
C
D

1
2. ## Thread: Help, required single formula, instead of using 3

by Marcelo Branco
Replies
5
Views
160

Maybe...

C
D
E
F
G
3. ## Thread: INDEX MATCH two criteria with SUMIF

by Marcelo Branco
Replies
4
Views
47

Try

A
B
C
D
E
F
4. ## Thread: Count Discrete Text values in column if other column value

by Marcelo Branco
Replies
1
Views
68

### Re: Count Discrete Text values in column if other column value

Welcome to Mr Excel forum

Try

A
B
C
D
5. ## Thread: Same user ID for multiple entries

by Marcelo Branco
Replies
1
Views
72

### Re: Same user ID for multiple entries

There many ways to achieve this. To avoid complex formulas I suggest...

A
B
C
D
E
6. ## Thread: Sumifs using two date ranges

by Marcelo Branco
Replies
4
Views
143

### Re: Sumifs using two date ranges

To calculate the overlap days between two ranges of dates you should use a formula like this
=MAX(0,MIN(DateTo, End) - MAX(DateFrom, Start) +1)

M.
7. ## Thread: Count If Visible Fields Advice

by Marcelo Branco
Replies
4
Views
210

### Re: Count If Visible Fields Advice

Simpler

Denominator
=SUBTOTAL(3,B6:B6345)

M.
8. ## Thread: Count If Visible Fields Advice

by Marcelo Branco
Replies
4
Views
210

### Re: Count If Visible Fields Advice

I think you should use a similar formula in the denominator, but excluding the condition ("Yes"), i.e.:
=SUMPRODUCT(SUBTOTAL(3,OFFSET(B6:B6345,ROW(B6:B6345)-ROW(B6),0,1)))

Otherwise, you will be...
9. ## Thread: vlookup with approximate values

by Marcelo Branco
Replies
2
Views
66

### Re: vlookup with approximate values

Maybe something like this...

A
B
C
D
E
F
10. ## Thread: sequence

by Marcelo Branco
Replies
7
Views
483

### Re: sequence

I don't remember exactly the logic i used 5 months ago ;) , but &"" avoids results equal to 0 (zero).
Try without &"" and you'll see

M.
11. ## Thread: Count If Visible Fields Advice

by Marcelo Branco
Replies
4
Views
210

### Re: Count If Visible Fields Advice

Count visible cells
https://exceljet.net/formula/count-visible-rows-in-a-filtered-list

Count visible cells with criteria
https://exceljet.net/formula/count-visible-rows-only-with-criteria

M.
12. ## Thread: If & Countifs

by Marcelo Branco
Replies
3
Views
91

### Re: If & Countifs

You are welcome. Thanks for the feedback.

M.
13. ## Thread: If & Countifs

by Marcelo Branco
Replies
3
Views
91

### Re: If & Countifs

Try

=COUNTIFS(Range,">="&Start Date, Range, "<="&End Date, AnotherRange,"Yes")

Range and AnotherRange must have same size

M.
14. ## Thread: Index Match Match.... It just wont work :(

by Marcelo Branco
Replies
3
Views
142

### Re: Index Match Match.... It just wont work :(

You are welcome. Glad to help.

M.
15. ## Thread: Index Match Match.... It just wont work :(

by Marcelo Branco
Replies
3
Views
142

### Re: Index Match Match.... It just wont work :(

Maybe...

=INDEX(Database!\$A\$4:\$EV\$7,MATCH(A8,Database!\$A\$4:\$A\$7,0),MATCH(B5,Database!\$A\$4:EV\$4,0))

M.
16. ## Thread: Sumproduct with Index / Match or Vlookup not returning array

by Marcelo Branco
Replies
11
Views
181

### Re: Sumproduct with Index / Match or Vlookup not returning array

You are welcome. Glad to help.

M.
17. ## Thread: Sumproduct with Index / Match or Vlookup not returning array

by Marcelo Branco
Replies
11
Views
181

### Re: Sumproduct with Index / Match or Vlookup not returning array

Just for fun...

Another
=SUMPRODUCT(--ISNUMBER(MATCH(B\$2:B\$6,F\$2:F\$6*(G\$2:G\$6=B\$11),0)),C\$2:C\$6,D\$2:D\$6)

M.
18. ## Thread: Sumproduct with Index / Match or Vlookup not returning array

by Marcelo Branco
Replies
11
Views
181

### Re: Sumproduct with Index / Match or Vlookup not returning array

Another (simpler) formula

=SUMPRODUCT(\$C\$2:\$C\$6,\$D\$2:\$D\$6,--(LOOKUP(B2:B6,F2:F6,G2:G6)=\$B\$11))
confirmed with just Enter

M.
19. ## Thread: Sumproduct with Index / Match or Vlookup not returning array

by Marcelo Branco
Replies
11
Views
181

### Re: Sumproduct with Index / Match or Vlookup not returning array

If i'm right maybe...

In order to force the INDEX function to return an array is tricky.
An extra "push" is needed - something like...
...
20. ## Thread: Sumproduct with Index / Match or Vlookup not returning array

by Marcelo Branco
Replies
11
Views
181

### Re: Sumproduct with Index / Match or Vlookup not returning array

Ron,

What is the expected result for C11= B?

Not sure i understood what you need, but i'm supposing you want to calculate the total for invoices 3 and 4 because such invoices belong to Category...
21. ## Thread: Countifs conundrum assistance needed

by Marcelo Branco
Replies
11
Views
220

### Re: Countifs conundrum assistance needed

You're welcome. Glad to help :)

M.
22. ## Thread: Countifs conundrum assistance needed

by Marcelo Branco
Replies
11
Views
220

### Re: Countifs conundrum assistance needed

New version - excludes closed orders

A
B
C
D
E
F
23. ## Thread: Countifs conundrum assistance needed

by Marcelo Branco
Replies
11
Views
220

### Re: Countifs conundrum assistance needed

Shouldn't the result be 8? It seems that rows 3, 4, 6, 7, 8, 9, 10 and 11 meet the criteria.

Something like this

A
B
C
D
24. ## Thread: Countifs conundrum assistance needed

by Marcelo Branco
Replies
11
Views
220

### Re: Countifs conundrum assistance needed

A sample of data that represents your real case along with the expected results would be helpful.

M.
25. ## Thread: Countifs conundrum assistance needed

by Marcelo Branco
Replies
11
Views
220

### Re: Countifs conundrum assistance needed

Try

A
B
C
D

1
Results 1 to 25 of 150
Page 1 of 6 1