# Search:

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

Page 1 of 6 1

1. ## Thread: INDEX MATCH two criteria with SUMIF

by Marcelo Branco
Replies
4
Views
37

Try

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

by Marcelo Branco
Replies
1
Views
63

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

Welcome to Mr Excel forum

Try

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

by Marcelo Branco
Replies
1
Views
66

### Re: Same user ID for multiple entries

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

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

by Marcelo Branco
Replies
4
Views
137

### 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.

by Marcelo Branco
Replies
4
Views
208

### Re: Count If Visible Fields Advice

Simpler

Denominator
=SUBTOTAL(3,B6:B6345)

M.

by Marcelo Branco
Replies
4
Views
208

### 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...
7. ## Thread: vlookup with approximate values

by Marcelo Branco
Replies
2
Views
61

### Re: vlookup with approximate values

Maybe something like this...

A
B
C
D
E
F

by Marcelo Branco
Replies
7
Views
480

### 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.

by Marcelo Branco
Replies
4
Views
208

### 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.
10. ## Thread: If & Countifs

by Marcelo Branco
Replies
3
Views
89

### Re: If & Countifs

You are welcome. Thanks for the feedback.

M.
11. ## Thread: If & Countifs

by Marcelo Branco
Replies
3
Views
89

### Re: If & Countifs

Try

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

Range and AnotherRange must have same size

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

by Marcelo Branco
Replies
3
Views
123

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

You are welcome. Glad to help.

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

by Marcelo Branco
Replies
3
Views
123

### 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.
14. ## Thread: Sumproduct with Index / Match or Vlookup not returning array

by Marcelo Branco
Replies
11
Views
174

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

You are welcome. Glad to help.

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

by Marcelo Branco
Replies
11
Views
174

### 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.
16. ## Thread: Sumproduct with Index / Match or Vlookup not returning array

by Marcelo Branco
Replies
11
Views
174

### 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.
17. ## Thread: Sumproduct with Index / Match or Vlookup not returning array

by Marcelo Branco
Replies
11
Views
174

### 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...
...
18. ## Thread: Sumproduct with Index / Match or Vlookup not returning array

by Marcelo Branco
Replies
11
Views
174

### 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...
19. ## Thread: Countifs conundrum assistance needed

by Marcelo Branco
Replies
11
Views
211

### Re: Countifs conundrum assistance needed

You're welcome. Glad to help :)

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

by Marcelo Branco
Replies
11
Views
211

### Re: Countifs conundrum assistance needed

New version - excludes closed orders

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

by Marcelo Branco
Replies
11
Views
211

### 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
22. ## Thread: Countifs conundrum assistance needed

by Marcelo Branco
Replies
11
Views
211

### Re: Countifs conundrum assistance needed

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

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

by Marcelo Branco
Replies
11
Views
211

Try

A
B
C
D

1
24. ## Thread: Ranking with Index Match Functions

by Marcelo Branco
Replies
2
Views
78

### Re: Ranking with Index Match Functions

Welcome to Mr Excel forum

Maybe something like this

A
B
C
D
25. ## Thread: Finding coverage of the range

by Marcelo Branco
Replies
6
Views
197

### Re: Finding coverage of the range

oops... typo

Formula in A1 copied across
=SUM(\$A2:A2)

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