Data is arranged as per the table in the image below. Need to summarize data based on the individual criteria on a daily basis.
-
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.– teylynJan 23, 2013 at 7:55
-
Hope this gives better idea....– roman_bullJan 23, 2013 at 8:36
1 Answer
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.
-
There are about 20 criteria like GS, HD, LP, etc. This definitely did work but wishing if there is any shorter formula... 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 Jan 23, 2013 at 10:58
-
2If 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