# Search:

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

Page 1 of 6 1

## Search: Search took 0.54 seconds.

by Marcelo Branco
Replies
1
Views
45

### Re: Conflict with

See if this example helps

A
B
C
D
E
F
2. ## Thread: Averageifs on Duplicate Columns

by Marcelo Branco
Replies
3
Views
82

### Re: Averageifs on Duplicate Columns

You are welcome. Thanks for the feedback.

M.
3. ## Thread: Averageifs on Duplicate Columns

by Marcelo Branco
Replies
3
Views
82

### Re: Averageifs on Duplicate Columns

Welcome to Mr Excel forum

Try
=AVERAGEIFS(INDEX(\$F\$5:\$EU\$37,MATCH("Mutual Fund Name",\$A5:\$A37,0),0),\$F\$4:\$EU\$4,"Peer Group Percentile")

Hope this helps

M.
4. ## Thread: Sumif with VBA

by Marcelo Branco
Replies
5
Views
162

### Re: Sumif with VBA

If you want to add numbers with decimal places, 100.17, 50.45 etc, you need a Double variable to store the result.

See...
5. ## Thread: Distinct Result from Multiple Countif

by Marcelo Branco
Replies
20
Views
380

### Re: Distinct Result from Multiple Countif

oops... the code above has a typo

Should be

Select Case vData(i, 1)
Case 238, 397 'IDs of interest
If vData(i, 4) = sCat Then dic(vData(i, 3)) = Empty
End Select
6. ## Thread: Distinct Result from Multiple Countif

by Marcelo Branco
Replies
20
Views
380

### Re: Distinct Result from Multiple Countif

New version - much faster, took only 0.5688 seconds to do the job!

Sub xTest()
Dim sCat As String
Dim dic As Object, vData As Variant, i As Long
Dim StartTime As Double,...
7. ## Thread: Distinct Result from Multiple Countif

by Marcelo Branco
Replies
20
Views
380

### Re: Distinct Result from Multiple Countif

Fazza,

A macro dealing with 550K rows took 6.0221 seconds to do the job.

Sub aTest()
Dim arrId As Variant, sCat As String
Dim dic As Object, vData As Variant, i As Long
Dim...
8. ## Thread: Distinct Result from Multiple Countif

by Marcelo Branco
Replies
20
Views
380

### Re: Distinct Result from Multiple Countif

Fazza,
I strongly agree, but as OP said...

So, I'm just trying to meet what he asked, ie include a new condition - with caveats: see post 6 and ps in post 14

M.
9. ## Thread: Break Revenue Down By Month - Start End Date and Amount

by Marcelo Branco
Replies
2
Views
84

### Re: Break Revenue Down By Month - Start End Date and Amount

Maybe...

=IF(AND(\$B2>=D\$1,\$A2<=DATE(YEAR(D\$1),MONTH(D\$1)+1,0)),MIN(\$B2,EOMONTH(D\$1,0))-MAX(\$A2,D\$1-1),0)*\$C2/(\$B2-\$A2)

M.

by Marcelo Branco
Replies
3
Views
53

### Re: Sequence flag

You are welcome. Thanks for the feedback.

M.

by Marcelo Branco
Replies
3
Views
53

### Re: Sequence flag

Counting 15 followed by 9
=SUMPRODUCT(--(B2:AE2=15),--(C2:AF2=9))

M.
12. ## Thread: Index Match Array inconsistent data

by Marcelo Branco
Replies
4
Views
48

### Re: Index Match Array inconsistent data

You are welcome. Thanks for the feedback.

To clean up your data take a look at
http://dmcritchie.mvps.org/excel/join.htm#trimall

M.
13. ## Thread: Index Match Array inconsistent data

by Marcelo Branco
Replies
4
Views
48

### Re: Index Match Array inconsistent data

Check if really AF51=AF52 and if AH51=AH52.
Try in empty cells
=AF51=AF52
=AH51=AH52

Both should return TRUE, otherwise i suspect there are extraneous characters (spaces?) in your data.

M.
14. ## Thread: Distinct Result from Multiple Countif

by Marcelo Branco
Replies
20
Views
380

### Re: Distinct Result from Multiple Countif

Maybe... (using rows 2:537802 as in your post 11)

A
B
C
D
E
F
15. ## Thread: Distinct Result from Multiple Countif

by Marcelo Branco
Replies
20
Views
380

### Re: Distinct Result from Multiple Countif

I can't see any data in column D (???)
Please, try to provide a data sample with data in column D, as you did in post 1, criteria and expected result.

M.
16. ## Thread: Distinct Result from Multiple Countif

by Marcelo Branco
Replies
20
Views
380

### Re: Distinct Result from Multiple Countif

Sorry, I'm not fully understanding what you need. Actually with 600K rows a complex formula should not be a good solution.

Maybe if you better describe your data, criteria and desired results,...

by Marcelo Branco
Replies
11
Views
177

### Re: Index Match Help

You are welcome. Glad we could help :)

M.
18. ## Thread: Distinct Result from Multiple Countif

by Marcelo Branco
Replies
20
Views
380

### Re: Distinct Result from Multiple Countif

Something like this

A
B
C
D
E
F
19. ## Thread: Distinct Result from Multiple Countif

by Marcelo Branco
Replies
20
Views
380

### Re: Distinct Result from Multiple Countif

What array formula did you try? I think the performance with 5K rows would be OK using an array formula to count unique occurrences of numbers like this:
=SUM(IF(FREQUENCY(....),1)) ...
20. ## Thread: MATCH/INDEX: Confirm a "Specific" Text, if any returns differ mark final return value as "No", if all match mark as "Yes"

by Marcelo Branco
Replies
5
Views
112

### Re: MATCH/INDEX: Confirm a "Specific" Text, if any returns differ mark final return value as "No", if all match mark as "Yes"

Maybe...

=IF(COUNTIFS('PRPO CLN'!\$E\$2:\$E\$5000,A1,'PRPO CLN'!L2:L5000,"Fully Released"),"Yes","No")

M.

by Marcelo Branco
Replies
11
Views
177

### Re: Index Match Help

Try

B2
=INDEX(tbl_Item[VERIFY],MATCH(1,INDEX(--ISNUMBER(SEARCH(tbl_Item[ITEM ID],[@[Folder ID]])),),0))

M.
22. ## Thread: SUMIF/SUMPRODUCT across multiple tables question

by Marcelo Branco
Replies
8
Views
114

### Re: SUMIF/SUMPRODUCT across multiple tables question

You are welcome. Thanks for the feedback.

M.

by Marcelo Branco
Replies
11
Views
177

Maybe...

A
B
C
D
E
F
24. ## Thread: SUMIF/SUMPRODUCT across multiple tables question

by Marcelo Branco
Replies
8
Views
114

### Re: SUMIF/SUMPRODUCT across multiple tables question

>0 is necessary because in my example row2 has two instances of "ado". It avoids double counting of this row.

See the formula suggested by Fluff - it also works and doesn't require >0

M.
25. ## Thread: SUMIF/SUMPRODUCT across multiple tables question

by Marcelo Branco
Replies
8
Views
114

### Re: SUMIF/SUMPRODUCT across multiple tables question

See if this example with a small data sample helps

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