Super User is a question and answer site for computer enthusiasts and power users. Join them; it only takes a minute:

Sign up
Here's how it works:
  1. Anybody can ask a question
  2. Anybody can answer
  3. The best answers are voted up and rise to the top

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

share|improve this question
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 '13 at 7:55
Hope this gives better idea.... – roman_bull Jan 23 '13 at 8:36
up vote 2 down vote accepted

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.

share|improve this answer
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 '13 at 9:07
Thank you Peter Albert – roman_bull Jan 23 '13 at 10:00
Next time, please tell this in advance! I adjusted the answer, as there is another way. – Peter Albert Jan 23 '13 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 '13 at 10:58
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)) – barry houdini Jan 23 '13 at 12:14

You must log in to answer this question.

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