Returning a sum of value base on the multiple inputted criteria

[yahm]

Hi,

I was doing a report regarding a list of member, provider and with its winning/losing. I am having a hard time figuring out what formula to use in order to get the data or value base on the criteria being input.

For example: (Report Sheet)
Cell C3 - need to input the Username.

Outcome:
It will calculate the Count, Bet Amount, Win/Loss per Provider and base on the Month. Inputted Month can be January - December or "All" . If "All", it will calculate the overall sum of the specific member.
Also, if inputted member was BLANK, it will then calculate also the Count, Bet Amount, Win/Loss per provider base on the inputted Month (January - December or "All")

Hope someone can help me on this one.

Thanks.

[MrShorty]

I am not an expert in them, but this really looks like a pivot table (http://www.excel-easy.com/data-analy...ot-tables.html ) should be the easiest way to get your report. I created a pivot table with:
column A (vendor) as the row labels
column B (date) as the column labels
column F (sum of count) as one value field
column I (sum of member bet amount) as a second value field
column J (sum of w/l) as the third value field

right click on the column labels and group by month. Then click on the column label dropdown and filter by Jun 2018.

[yahm]

Thanks for your response but what the outcome that I was trying to figure out is base on the Inputted Username and then the Month.
The one you show is for the Monthly criteria..

thanks

[MrShorty]

I must not be understanding your distinction between "monthly criteria" and "inputted month". At the end of the sequence of events I described, you should have a report for the sum of each username during Jun 2018 (or July 2018 if you select July 2018 in the filter step). Perhaps if you explain in more detail what you are trying to do, and why a pivot table does not work for what you want.

[yahm]

hmm.. ok.. sorry.. if I was only looking for the month, then the pivot table was fine. but I also want to input the username. Just like the one in the Report sheet on the sample data.

[MrShorty]

A pivot table can also filter by row label. Select the dropdown for the vendor name row labels and select the desired name(s) from the dropdown list.

[yahm]

ok, now its working.

But aside from Pivot Table, is there any formula that can be use on that?
Coz I was also trying to use Sumproduct, but its so complicated...

[Sam Capricci]

I've been playing with filling in your report tab so I thought I'd add it.
so, a couple adjustments I made, one is to change your month in C5 to an actual date but formatted as month (7/1/2018) and I am ignoring your year value in G5.
so here it is attached with formulas in C8, D8 and E8 in the report tab.
they are...
C8 =SUMIFS(DATA!F:F,DATA!A:A,B8,DATA!B:B,">="&$C$5,DATA!B:B,"<="&EOMONTH($C$5,0))
D8 =SUMIFS(DATA!I:I,DATA!A:A,B8,DATA!B:B,">="&$C$5,DATA!B:B,"<="&EOMONTH($C$5,0))
E8 =SUMIFS(DATA!J:J,DATA!A:A,B8,DATA!B:B,">="&$C$5,DATA!B:B,"<="&EOMONTH($C$5,0))

[yahm]

still the Username was not included.. I have to input also the specific username then if I just leave it Blank, then it will calculate the overall Count, Bet amount, Win/Loss per Provider.

[MrShorty]

I tend to use SUMIFS() before SUMPRODUCT(). https://support.office.com/en-us/art...6-611cebce642b something like =SUMIFS(column I, column A, vendor name,column B,>=1st of month,columnB,<=last of month)

[yahm]

I tend to use SUMIFS() before SUMPRODUCT(). https://support.office.com/en-us/art...6-611cebce642b something like =SUMIFS(column I, column A, vendor name,column B,>=1st of month,columnB,<=last of month)

Can you put the exact formula as example.. I cannot understand how to do it..

thanks

[MrShorty]

Sambo kid put an exact example up, so I won't bother.

Adding username should be as easy as adding another criteria to the SUMIFS() -- =E8 =SUMIFS(DATA!J:J,DATA!A:A,B8,DATA!B:B,">="&$C$5,DATA!B:B,"<="&EOMONTH($C$5,0),DATA!E:E,C3). Similar for other formulas.

[yahm]

Sambo kid put an exact example up, so I won't bother.

Adding username should be as easy as adding another criteria to the SUMIFS() -- =E8 =SUMIFS(DATA!J:J,DATA!A:A,B8,DATA!B:B,">="&$C$5,DATA!B:B,"<="&EOMONTH($C$5,0),DATA!E:E,C3). Similar for other formulas.

its working.. but if I leave the Username blank, it should give me the overall total per provider and still base on the Month being input. Base on the formula you gave, when the Username was blank, it give me a "0" total.

[Sam Capricci]

Thanks MrShorty, I had just reread the first post and noticed I forgot to include the username part and was just working on it. And I was also going to throw in an IF then part in case the date changes from a specific month to all, something like this at the beginning... =IF($C$5="All",SUM(DATA!F:F), then the rest.

[Sam Capricci]

this in C8... =IF($C$3="",SUMIF(DATA!A:A,B8,DATA!F:F),SUMIFS(DATA!F:F,DATA!A:A,B8,DATA!E:E,$C$3,DATA!B:B,">="&$C$5,DATA!B:B,"<="&EOMONTH($C$5,0)))
this in D8... =IF($C$3="",SUMIF(DATA!A:A,B8,DATA!I:I),SUMIFS(DATA!I:I,DATA!A:A,B8,DATA!E:E,$C$3,DATA!B:B,">="&$C$5,DATA!B:B,"<="&EOMONTH($C$5,0)))
this in E8... =IF($C$3="",SUMIF(DATA!A:A,B8,DATA!J:J),SUMIFS(DATA!J:J,DATA!A:A,B8,DATA!E:E,$C$3,DATA!B:B,">="&$C$5,DATA!B:B,"<="&EOMONTH($C$5,0)))

though I'm not sure where "All" fits in, where would you put it?

[yahm]

ok.. here's the IF's:

a. If username=blank and month=all, then it should calculate the overall Count, Bet AMount, Win/loss
b. If username=blank and month=June or July or August... , then it should calculate the Count, Bet Amount, Win/loss
c. If username="dewa19" and month=all, then it should calculate the overall count, bet amount, win/loss for that username
d. If username="dewa19" and month=June or July or August..., then it should calculate the count, bet amount, win/loss for that username and the month inputted.

[Sam Capricci]

overall count, this in C8
=IF(AND($C$3="",$C$5="all"),SUM(DATA!F:F),IF($C$3="",SUMIFS(DATA!F:F,DATA!A:A,$B8,DATA!B:B,">="&$C$5,DATA!B:B,"<="&EOMONTH($C$5,0)),IF($C$5="all",SUMIFS(DATA!F:F,DATA!A:A,$B8,DATA!E:E,$C$3),SUMIFS(DATA!F:F,DATA!A:A,$B8,DATA!B:B,">="&$C$5,DATA!B:B,"<="&EOMONTH($C$5,0),DATA!E:E,$C$3))))
this in D8...
=IF(AND($C$3="",$C$5="all"),SUM(DATA!I:I),IF($C$3="",SUMIFS(DATA!I:I,DATA!A:A,$B8,DATA!B:B,">="&$C$5,DATA!B:B,"<="&EOMONTH($C$5,0)),IF($C$5="all",SUMIFS(DATA!I:I,DATA!A:A,$B8,DATA!E:E,$C$3),SUMIFS(DATA!I:I,DATA!A:A,$B8,DATA!B:B,">="&$C$5,DATA!B:B,"<="&EOMONTH($C$5,0),DATA!E:E,$C$3))))
this in E8...
=IF(AND($C$3="",$C$5="all"),SUM(DATA!J:J),IF($C$3="",SUMIFS(DATA!J:J,DATA!A:A,$B8,DATA!B:B,">="&$C$5,DATA!B:B,"<="&EOMONTH($C$5,0)),IF($C$5="all",SUMIFS(DATA!J:J,DATA!A:A,$B8,DATA!E:E,$C$3),SUMIFS(DATA!J:J,DATA!A:A,$B8,DATA!B:B,">="&$C$5,DATA!B:B,"<="&EOMONTH($C$5,0),DATA!E:E,$C$3))))


IF the overall count needs to focus on the provider too while C3 is blank and C5 is all then this in C8...
=IF(AND($C$3="",$C$5="all"),SUMIF(DATA!A:A,$B8,DATA!F:F),IF($C$3="",SUMIFS(DATA!F:F,DATA!A:A,$B8,DATA!B:B,">="&$C$5,DATA!B:B,"<="&EOMONTH($C$5,0)),IF($C$5="all",SUMIFS(DATA!F:F,DATA!A:A,$B8,DATA!E:E,$C$3),SUMIFS(DATA!F:F,DATA!A:A,$B8,DATA!B:B,">="&$C$5,DATA!B:B,"<="&EOMONTH($C$5,0),DATA!E:E,$C$3))))
this in D8...
=IF(AND($C$3="",$C$5="all"),SUMIF(DATA!A:A,$B8,DATA!I:I),IF($C$3="",SUMIFS(DATA!I:I,DATA!A:A,$B8,DATA!B:B,">="&$C$5,DATA!B:B,"<="&EOMONTH($C$5,0)),IF($C$5="all",SUMIFS(DATA!I:I,DATA!A:A,$B8,DATA!E:E,$C$3),SUMIFS(DATA!I:I,DATA!A:A,$B8,DATA!B:B,">="&$C$5,DATA!B:B,"<="&EOMONTH($C$5,0),DATA!E:E,$C$3))))
and this in E8...
=IF(AND($C$3="",$C$5="all"),SUMIF(DATA!A:A,$B8,DATA!J:J),IF($C$3="",SUMIFS(DATA!J:J,DATA!A:A,$B8,DATA!B:B,">="&$C$5,DATA!B:B,"<="&EOMONTH($C$5,0)),IF($C$5="all",SUMIFS(DATA!J:J,DATA!A:A,$B8,DATA!E:E,$C$3),SUMIFS(DATA!J:J,DATA!A:A,$B8,DATA!B:B,">="&$C$5,DATA!B:B,"<="&EOMONTH($C$5,0),DATA!E:E,$C$3))))

[yahm]

Great! its now working..

One last thing, is it possible you can also do it by daily basis like from a specific date to a certain date. Example from July 01, 2018 to July 18, 2018 then same result as the monthly basis.

thanks

[Sam Capricci]

does the overall count need to also include the provider in column B of the report tab?

[Sam Capricci]

where would you put the "range" and which set of formulas do you like, the first that do not depend on the provider or the second that do take the provider into account?
So, for the "range" will the first date be in one cell and the second date in another and if you want the whole month as the formula has now, what will be in the cell for the second date?
Answer both questions and I'll see if I can figure it out.
AND, you don't need to post the quotes unless there is something specific you need to address.

[AliGW]

@Yahm

Please don't quote whole posts, especially when you are responding to the one immediately preceding your own - it's just clutter. It's OK to quote if you are responding to a post out of sequence, but limit quoted content to a few relevant lines that makes clear to whom and what you are responding. Thanks!

For normal conversational replies, try using the QUICK REPLY box below.

[yahm]

Ok. sorry.. thanks

[Sam Capricci]

Ok, this should cover a range for you.
First, I am assuming the following: User Name goes in cell C3 of the report tab.
Second, first month date goes into C5 in usual month format like m/dd/yyyy OR All.
Third, if you want a range of dates then put the second date in D5 in same format you use, OR leave blank.

first formula for C8 in Report tab and dragged down...

=IF(AND($C$3="",$C$5="all",$D$5=""),SUMIF(DATA!A:A,B8,DATA!F:F),IF(AND($C$3="",$C$5<>"all",$D$5=""),SUMIFS(DATA!F:F,DATA!A:A,B8,DATA!B:B,">="&$C$5,DATA!B:B,"<="&EOMONTH($C$5,0)),IF(AND($C$3<>"",$C$5="all",$D$5=""),SUMIFS(DATA!F:F,DATA!A:A,B8,DATA!E:E,$C$3),IF(AND($C$3<>"",$C$5<>"all",$C$5<>"",$D$5=""),SUMIFS(DATA!F:F,DATA!A:A,B8,DATA!E:E,$C$3,DATA!B:B,">="&$C$5,DATA!B:B,"<="&EOMONTH($C$5,0)),IF(AND($C$3<>"",$C$5<>"",$D$5<>""),SUMIFS(DATA!F:F,DATA!A:A,B8,DATA!E:E,$C$3,DATA!B:B,">="&$C$5,DATA!B:B,"<="&$D$5),"")))))  

second formula in D8 and dragged down...

=IF(AND($C$3="",$C$5="all",$D$5=""),SUMIF(DATA!A:A,B8,DATA!I:I),IF(AND($C$3="",$C$5<>"all",$D$5=""),SUMIFS(DATA!I:I,DATA!A:A,B8,DATA!B:B,">="&$C$5,DATA!B:B,"<="&EOMONTH($C$5,0)),IF(AND($C$3<>"",$C$5="all",$D$5=""),SUMIFS(DATA!I:I,DATA!A:A,B8,DATA!E:E,$C$3),IF(AND($C$3<>"",$C$5<>"all",$C$5<>"",$D$5=""),SUMIFS(DATA!I:I,DATA!A:A,B8,DATA!E:E,$C$3,DATA!B:B,">="&$C$5,DATA!B:B,"<="&EOMONTH($C$5,0)),IF(AND($C$3<>"",$C$5<>"",$D$5<>""),SUMIFS(DATA!I:I,DATA!A:A,B8,DATA!E:E,$C$3,DATA!B:B,">="&$C$5,DATA!B:B,"<="&$D$5),"")))))  

third formula in E8 and dragged down...

=IF(AND($C$3="",$C$5="all",$D$5=""),SUMIF(DATA!A:A,B8,DATA!J:J),IF(AND($C$3="",$C$5<>"all",$D$5=""),SUMIFS(DATA!J:J,DATA!A:A,B8,DATA!B:B,">="&$C$5,DATA!B:B,"<="&EOMONTH($C$5,0)),IF(AND($C$3<>"",$C$5="all",$D$5=""),SUMIFS(DATA!J:J,DATA!A:A,B8,DATA!E:E,$C$3),IF(AND($C$3<>"",$C$5<>"all",$C$5<>"",$D$5=""),SUMIFS(DATA!J:J,DATA!A:A,B8,DATA!E:E,$C$3,DATA!B:B,">="&$C$5,DATA!B:B,"<="&EOMONTH($C$5,0)),IF(AND($C$3<>"",$C$5<>"",$D$5<>""),SUMIFS(DATA!J:J,DATA!A:A,B8,DATA!E:E,$C$3,DATA!B:B,">="&$C$5,DATA!B:B,"<="&$D$5),"")))))  

This has taken me a few hours so please no more changes Hope that covers what you need.

[yahm]

Its all good now. Really appreciate all your effort here

Thank you so much and keep on sharing your knowledge here.

Cheers!!!