0

Data is arranged as per the table in the image below. Need to summarize data based on the individual criteria on a daily basis.

enter image description here

2
  • Can you possibly post a table that has some numeric values and mock up the expected result? it is not apparent where the formula is supposed to go and where the data to be summed is coming from.
    – teylyn
    Jan 23, 2013 at 7:55
  • Hope this gives better idea....
    – roman_bull
    Jan 23, 2013 at 8:36

1 Answer 1

2

You can do this with simple COUNTIFS:

C12: =COUNTIFS($B$3:$B$8,$B12;C$3:C$8,"GS")+COUNTIFS($B$3:$B$8,$B12;C$3:C$8,"HD")

C13: =COUNTIFS($B$3:$B$8,$B13;C$3:C$8,"GS")+COUNTIFS($B$3:$B$8,$B13;C$3:C$8,"HT")

C14: =COUNTIFS($B$3:$B$8,$B14;C$3:C$8,"CS")

If you have a lot of criteria, you can use an array formula instead:

In A12, store the list of criteria, separated by comma, i.e. GS, HD. Same for A13, A14, etc. Then, in C12, use the following formula:

=SUM(($B$2:$B$8=$B12)*(IFERROR(SEARCH(" "&C$2:C$8&","," "&$A12&","),0)0))

Enter this as an array formula, i.e. press Ctrl-Shift-Enter.

4
  • There are about 20 criteria like GS, HD, LP, etc. This definitely did work but wishing if there is any shorter formula...
    – roman_bull
    Jan 23, 2013 at 9:07
  • Next time, please tell this in advance! I adjusted the answer, as there is another way. Jan 23, 2013 at 10:39
  • Never mind, it was just one time effort to create the formula and it is working fine...!! Thank you once again Peter Albert
    – roman_bull
    Jan 23, 2013 at 10:58
  • 2
    If you put the 20 criteria in separate cells, e.g. Z1:Z20 you can use COUNTIFS like this: =SUMPRODUCT(COUNTIFS($B$3:$B$8,$B12;C$3:C$8,Z1:Z20)) Jan 23, 2013 at 12:14

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .