SOLVED

Formula help needed

Copper Contributor

Sample data only:

Table 1

Malen0125_1-1624696370484.png

Pls help what formula to use if data from table 1 be consolidate in the table 2 using formula

- how to generate the count of FSS/PSS/RSM/and so on who are under quarantine/RT-PCR+/and so on to table1 (data entry).

 

Table 2

Malen0125_3-1624696431030.png

Thank you.

 

 

 

 

13 Replies

@Malen0125 

It looks like job for Power Query if transform entire table. If one by one perhaps SUMIFS() works. But it's hard to be more concrete without sample file and knowing on which version of Excel you are.

use sumproduct function for this,

if you could share sample file in excel, i can help out.

Hi @Malen0125 ,

 

Ang imo table ge convert nko using power query from this:

Yea_So_0-1624763789670.png

to this:

Yea_So_1-1624763813495.png

nya gamit ko ug ani na formula:

=IF(SUMIFS(_PQ1[Value],_PQ1[Designation],Table2!E$1,_PQ1[Attribute],Table2!$B3)=0,"",SUMIFS(_PQ1[Value],_PQ1[Designation],Table2!E$1,_PQ1[Attribute],Table2!$B3))

sa table 2

Yea_So_2-1624763905500.png

kung naa ka pangutana pm lang dire

file is attached

cheers

 

Hi, @Yea_So 

 

Wow! this is great. Thank you so much. 

 

I have questions: 1. para ma count nya ang entry sa table , necessary na in convert nya sa power query table? same like validation table.

 

2. Is it possible na if naa ko entry in table (new case) automatice sya mo count didto sa table 2 (conso table).

 

 

 

 

 

Hi, @Ravitosh Kumar 

Yes, i can share it with you. Pls see attached document. hope to hear from you soonest.

hi. Sergei Baklan
i have sample file. i will send it to you. is that okay for your?
hi, Sergei Baklan
I have sample file, however not sure if the version of my excel is v10.
best response confirmed by allyreckerman (Microsoft)
Solution

Hi @Malen0125,

 

I have questions: 1. para ma count nya ang entry sa table , necessary na in convert nya sa power query table? same like validation table.

A. Dili necessary pero it will make it mas sayon.  I think the validation table is a static table, meaning it stays the same size dimension only to present values that are aggregated (summarized) related to the details of gathering information, typically most corporate businesses use a pivot table or data model.  Since your data set isn't large enough to warrant an expansion of the validation table, you can have it as is, where it is and no need to change it until there's a need for expansion where you can decide on whether you want to used a pivot table that is based on a data model.

2. Is it possible na if naa ko entry in table (new case) automatice sya mo count didto sa table 2 (conso table).

A. Automatic=Dynamic. Para ma dynamic ang update sa table kinahanglan gamiton nmo ang excel formatted table kay pg mg add ka ug new entry mg expand mn ang table automatic, pati ang range sa formula automatic xa mg update.

@Malen0125 i have converted data entry sheet to table. worked file attached.

 

 

=SUMPRODUCT((Table1[Designation]=E$5)*(Table1[[#Headers],[Anti-Body + / Antigen +]:[w/ FTW]]=$C10)*Table1[[Anti-Body + / Antigen +]:[w/ FTW]])

 

summary data shows & works fine with additional data i tested.

it is intersection of all the 3 conditions: (designation) with (type of test) with (data values).

Hi, @Yea_So 

 

This is Great! i learn something new from excel with you, using  various formula na hindi ko na gamit.

Thank you so much for sharing your knowledge in excel. Talk to you soon. 

 

hi, Ravitosh Kumar
This is perfect, thank so much for sharing your skills in excel. This is a great help to me. Talk to you soon.

Hi @Malen0125,

 

wlay sapayan.

 

cheers

thanks.
please mark as best response if solved your problem
1 best response

Accepted Solutions
best response confirmed by allyreckerman (Microsoft)
Solution

Hi @Malen0125,

 

I have questions: 1. para ma count nya ang entry sa table , necessary na in convert nya sa power query table? same like validation table.

A. Dili necessary pero it will make it mas sayon.  I think the validation table is a static table, meaning it stays the same size dimension only to present values that are aggregated (summarized) related to the details of gathering information, typically most corporate businesses use a pivot table or data model.  Since your data set isn't large enough to warrant an expansion of the validation table, you can have it as is, where it is and no need to change it until there's a need for expansion where you can decide on whether you want to used a pivot table that is based on a data model.

2. Is it possible na if naa ko entry in table (new case) automatice sya mo count didto sa table 2 (conso table).

A. Automatic=Dynamic. Para ma dynamic ang update sa table kinahanglan gamiton nmo ang excel formatted table kay pg mg add ka ug new entry mg expand mn ang table automatic, pati ang range sa formula automatic xa mg update.

View solution in original post