# Search:

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

Page 1 of 6 1

1. ## Thread: Sumifs using two date ranges

by Marcelo Branco
Replies
4
Views
111

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

### Re: Count If Visible Fields Advice

Simpler

Denominator
=SUBTOTAL(3,B6:B6345)

M.

by Marcelo Branco
Replies
4
Views
185

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

by Marcelo Branco
Replies
2
Views
57

### Re: vlookup with approximate values

Maybe something like this...

A
B
C
D
E
F

by Marcelo Branco
Replies
7
Views
468

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

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

by Marcelo Branco
Replies
3
Views
85

### Re: If & Countifs

You are welcome. Thanks for the feedback.

M.
8. ## Thread: If & Countifs

by Marcelo Branco
Replies
3
Views
85

### Re: If & Countifs

Try

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

Range and AnotherRange must have same size

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

by Marcelo Branco
Replies
3
Views
122

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

You are welcome. Glad to help.

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

by Marcelo Branco
Replies
3
Views
122

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

by Marcelo Branco
Replies
11
Views
169

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

You are welcome. Glad to help.

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

by Marcelo Branco
Replies
11
Views
169

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

by Marcelo Branco
Replies
11
Views
169

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

by Marcelo Branco
Replies
11
Views
169

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

by Marcelo Branco
Replies
11
Views
169

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

by Marcelo Branco
Replies
11
Views
210

### Re: Countifs conundrum assistance needed

You're welcome. Glad to help :)

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

by Marcelo Branco
Replies
11
Views
210

### Re: Countifs conundrum assistance needed

New version - excludes closed orders

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

by Marcelo Branco
Replies
11
Views
210

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

by Marcelo Branco
Replies
11
Views
210

### Re: Countifs conundrum assistance needed

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

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

by Marcelo Branco
Replies
11
Views
210

Try

A
B
C
D

1
21. ## 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
22. ## Thread: Finding coverage of the range

by Marcelo Branco
Replies
6
Views
196

### Re: Finding coverage of the range

oops... typo

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

M.
23. ## Thread: Finding coverage of the range

by Marcelo Branco
Replies
6
Views
196

### Re: Finding coverage of the range

Maybe something like this...

A
B
C
D
E
F
24. ## Thread: Sumproduct Multiple criteria same column

by Marcelo Branco
Replies
13
Views
272

### Re: Sumproduct Multiple criteria same column

Yes, initially I did not understand either, but observing the desired results I suggested a formula. Looks like I got it right :)

M.
25. ## Thread: Sumproduct Multiple criteria same column

by Marcelo Branco
Replies
13
Views
272

### Re: Sumproduct Multiple criteria same column

Maybe...

A
B
C
D
E
F
Results 1 to 25 of 150
Page 1 of 6 1