How to use countif/Sumif where Date given as column header In MySQL or Excel

Question

I have a table like this

I want output as Dates in one column and count of mobile numbers on each date which has crossed 150 MB of usage along with the sum of their usage in the next column.

SAMPLE INPUT:

mobile_no,1-Sep-17,2-Sep-17,3-Sep-17,4-Sep-17,5-Sep-17
33045,50,110,200,100,34
33046,23,123,30,480,230
33047,102,217,23,145,186
33048,165,201,230,230,149
33049,22,229,160,71,173

Could you check if the expected output you showed is based on the example input. Based on your logic, I am not getting the same output for some Dates — akrun, Sep 14 '17 at 06:41
Please provide [reproducible example](http://stackoverflow.com/questions/5963269) — zx8754, Sep 14 '17 at 06:41
Tx, but if I look into your example, For sep_2, there are only 3 values that are greater than 150. So, it is not clear how you are getting 881 — akrun, Sep 14 '17 at 07:02
@Dau_uaD - Are you okay with solution in excel using formula. — Mrig, Sep 14 '17 at 07:31

score 4 · Answer 1 · answered Sep 14 '17 at 07:37

In Cell H2 enter the following formula

=OFFSET($A$1,0,ROW()-1)

Then in Cell I2 enter

=SUMPRODUCT(($B$1:$F$1=H2)*($B$2:$F$6>150))

Finally in Cell J2 enter

=SUMPRODUCT(($B$1:$F$1=H2)*($B$2:$F$6>150)*($B$2:$F$6))

Drag/Copy down formulas as required. Change the range as per your data. See image for reference.

akrun · Answer 2 · 2017-09-14T07:06:19.997

1

In R, we can gather it to 'long' format, then grouped by the 'Date' column, get the number of 'mobile_no' and also the sum of 'val'

library(tidyverse)
df1 %>%
    gather(Date, val, -mobile_no) %>%
    filter(val > 150) %>%
    group_by(Date) %>%
    summarise(Count = n(), Sum_of_Usage = sum(val))
# A tibble: 5 x 3
#      Date Count Sum_of_Usage
#      <chr> <int>        <dbl>
#1 1-Sep-17     1          165
#2 2-Sep-17     3          647
#3 3-Sep-17     3          590
#4 4-Sep-17     2          710
#5 5-Sep-17     3          589

edited Sep 14 '17 at 07:06

answered Sep 14 '17 at 06:32

akrun

874,273
37
540
662

1

It works fine. What if I want to group it in buckets like 0-50, 50-100,100-150 and 150+ – Dau_uaD Sep 14 '17 at 07:37
@Dau_uaD You can use `cut` to create the groups – akrun Sep 14 '17 at 07:38

How to use countif/Sumif where Date given as column header In MySQL or Excel

2 Answers2